DECLARE @x xml; SET @x = N'<Employees> <Employee ID="101"> <Name>Jacob</Name> <Department>IT</Department> </Employee> <Employee ID="354"> <Name>Steve</Name> <Department>IT</Department> </Employee> <Employee ID="456"> <Name>Bob</Name> <Department>IT</Department> </Employee> <Employee ID="478"> <Name>Joe</Name> <Department>IT</Department> </Employee> <Employee ID="981"> <Name>Louis</Name> <Department>IT</Department> </Employee> </Employees>'; -- Total count of <Employee> Nodes DECLARE @max int, @i int; SELECT @max = @x.value('count(/Employees/Employee)', 'int'); -- Set counter variable to 1 SET @i = 1; -- variable to store employee name DECLARE @EmpNode xml; -- loop starts WHILE @i <= @max BEGIN -- select "Name" to the variable SELECT @EmpNode = @x.query('/Employees/Employee[sql:variable("@i")]'); -- print the name SELECT @EmpNode; -- increment counter SET @i = @i + 1 ; END GO>>>Here is an example of processing an XML in a stored procedure:
>>>declare @myxml as XML = N' >>><TABLENAME> >>> <Fld Name="Field1">ValueInField1</Fld> >>> <Fld Name="Field2">ValueInField2</Fld> >>> <Fld Name="Field3">ValueInField3</Fld> >>></TABLENAME> ' >>> >>>-- SQL Select to get the values >>>SELECT [Field].value('.','varchar(100)') as FldValue, [Field].value('@Name','varchar(20)') as FldName >>> FROM @myxml.nodes('/TABLENAME/Fld') Fld25 ([Field]) >>> >>>-- The Results window will the following: >>>FldValue FldName >>>ValueInField1 Field1 >>>ValueinFiedl2 Field2 >>>ValueInField3 Field3 >>>>>>