How to iterate in xml data in loop without cursor

Category: sql server xml

Question

Sudip_inn on Sun, 18 Nov 2018 12:50:01


we know that we can use cursor to iterate in data for normal table and xml too. i use loop to iterate in table data like below code. 

CREATE TABLE #Employee
(Id INT, Name NVARCHAR(100), Status TINYINT)
GO
INSERT INTO #Employee ( Id, Name, Status)
Values (1, 'Basavaraj Biradar', 0),
        (2, 'Shree Biradar', 0),
        (3, 'Kalpana Biradar', 0)
        

DECLARE @LoopCounter INT , @MaxEmployeeId INT, 
        @EmployeeName NVARCHAR(100)
SELECT @LoopCounter = min(id) , @MaxEmployeeId = max(Id) 
FROM #Employee
 
WHILE(@LoopCounter IS NOT NULL
      AND @LoopCounter <= @MaxEmployeeId)
BEGIN
   SELECT @EmployeeName = Name
   FROM #Employee WHERE Id = @LoopCounter
    
   PRINT @EmployeeName  
   SET @LoopCounter  = @LoopCounter  + 1        
END

suppose i have huge customer data in xml format in which i need to iterate without cursor. tell me how could i iterate in my xml without cursor rather i like to use loop.

sample xml data 

<?xml version="1.0" encoding="utf-8"?>  
<Root xmlns="http://www.adventure-works.com">  
  <Customers>  
    <Customer CustomerID="GREAL">  
      <CompanyName>Great Lakes Food Market</CompanyName>  
      <ContactName>Howard Snyder</ContactName>  
      <ContactTitle>Marketing Manager</ContactTitle>  
      <Phone>(503) 555-7555</Phone>  
      <FullAddress>  
        <Address>2732 Baker Blvd.</Address>  
        <City>Eugene</City>  
        <Region>OR</Region>  
        <PostalCode>97403</PostalCode>  
        <Country>USA</Country>  
      </FullAddress>  
    </Customer>  
    <Customer CustomerID="HUNGC">  
      <CompanyName>Hungry Coyote Import Store</CompanyName>  
      <ContactName>Yoshi Latimer</ContactName>  
      <ContactTitle>Sales Representative</ContactTitle>  
      <Phone>(503) 555-6874</Phone>  
      <Fax>(503) 555-2376</Fax>  
      <FullAddress>  
        <Address>City Center Plaza 516 Main St.</Address>  
        <City>Elgin</City>  
        <Region>OR</Region>  
        <PostalCode>97827</PostalCode>  
        <Country>USA</Country>  
      </FullAddress>  
    </Customer>  
    <Customer CustomerID="LAZYK">  
      <CompanyName>Lazy K Kountry Store</CompanyName>  
      <ContactName>John Steel</ContactName>  
      <ContactTitle>Marketing Manager</ContactTitle>  
      <Phone>(509) 555-7969</Phone>  
      <Fax>(509) 555-6221</Fax>  
      <FullAddress>  
        <Address>12 Orchestra Terrace</Address>  
        <City>Walla Walla</City>  
        <Region>WA</Region>  
        <PostalCode>99362</PostalCode>  
        <Country>USA</Country>  
      </FullAddress>  
    </Customer>  
    <Customer CustomerID="LETSS">  
      <CompanyName>Let's Stop N Shop</CompanyName>  
      <ContactName>Jaime Yorres</ContactName>  
      <ContactTitle>Owner</ContactTitle>  
      <Phone>(415) 555-5938</Phone>  
      <FullAddress>  
        <Address>87 Polk St. Suite 5</Address>  
        <City>San Francisco</City>  
        <Region>CA</Region>  
        <PostalCode>94117</PostalCode>  
        <Country>USA</Country>  
      </FullAddress>  
    </Customer>  
  </Customers>  
  </Root>  
  

thanks

Replies

Yitzhak Khabinsky on Sun, 18 Nov 2018 15:47:02


Hi Sudip,

It is possible to loop through XML via XPath position() function.

declare @xml XML = '<?xml version="1.0" encoding="utf-8"?>
<Root xmlns="http://www.adventure-works.com">
	<Customers>
		<Customer CustomerID="GREAL">
			<CompanyName>Great Lakes Food Market</CompanyName>
			<ContactName>Howard Snyder</ContactName>
			<ContactTitle>Marketing Manager</ContactTitle>
			<Phone>(503) 555-7555</Phone>
			<FullAddress>
				<Address>2732 Baker Blvd.</Address>
				<City>Eugene</City>
				<Region>OR</Region>
				<PostalCode>97403</PostalCode>
				<Country>USA</Country>
			</FullAddress>
		</Customer>
		<Customer CustomerID="HUNGC">
			<CompanyName>Hungry Coyote Import Store</CompanyName>
			<ContactName>Yoshi Latimer</ContactName>
			<ContactTitle>Sales Representative</ContactTitle>
			<Phone>(503) 555-6874</Phone>
			<Fax>(503) 555-2376</Fax>
			<FullAddress>
				<Address>City Center Plaza 516 Main St.</Address>
				<City>Elgin</City>
				<Region>OR</Region>
				<PostalCode>97827</PostalCode>
				<Country>USA</Country>
			</FullAddress>
		</Customer>
		<Customer CustomerID="LAZYK">
			<CompanyName>Lazy K Kountry Store</CompanyName>
			<ContactName>John Steel</ContactName>
			<ContactTitle>Marketing Manager</ContactTitle>
			<Phone>(509) 555-7969</Phone>
			<Fax>(509) 555-6221</Fax>
			<FullAddress>
				<Address>12 Orchestra Terrace</Address>
				<City>Walla Walla</City>
				<Region>WA</Region>
				<PostalCode>99362</PostalCode>
				<Country>USA</Country>
			</FullAddress>
		</Customer>
		<Customer CustomerID="LETSS">
			<CompanyName>Let''s Stop N Shop</CompanyName>
			<ContactName>Jaime Yorres</ContactName>
			<ContactTitle>Owner</ContactTitle>
			<Phone>(415) 555-5938</Phone>
			<FullAddress>
				<Address>87 Polk St. Suite 5</Address>
				<City>San Francisco</City>
				<Region>CA</Region>
				<PostalCode>94117</PostalCode>
				<Country>USA</Country>
			</FullAddress>
		</Customer>
	</Customers>
</Root>';

-- count total number of rows
DECLARE @cnt INT, @i INT;
SET @cnt = @xml.value('declare namespace ns1="http://www.adventure-works.com"; count(/ns1:Root/ns1:Customers/ns1:Customer)', 'int');
--SELECT @cnt;

-- loop row by row
SET @i = 1;
WHILE @i <= @cnt BEGIN
	;WITH XMLNAMESPACES (DEFAULT 'http://www.adventure-works.com'), rs AS
    (
		SELECT col.value('(@CustomerID)[1]','VARCHAR(50)') AS CustomerID
		, col.value('(CompanyName)[1]','VARCHAR(100)') AS CompanyName
		, col.value('(ContactName)[1]','VARCHAR(100)') AS ContactName
		-- , add the rest
		FROM @xml.nodes('/Root/Customers/Customer[position() = sql:variable("@i")]') AS tab(col)
	)
	SELECT * FROM rs;

   SET @i += 1;
END


Sudip_inn on Mon, 19 Nov 2018 19:36:40


if Root has no namespaces like <Root> then what alteration need to do in this line of code

SET @cnt = @xml.value('declare namespace ns1="http://www.adventure-works.com"; count(/ns1:Root/ns1:Customers/ns1:Customer)', 'int');

;WITH XMLNAMESPACES (DEFAULT 'http://www.adventure-works.com'), rs AS

please give changed code if there is no namespace concept in Root. thanks

Yitzhak Khabinsky on Mon, 19 Nov 2018 23:21:47


Hi Sudip,

Without the namespace declaration it would be like follows:

SET @cnt = @xml.value('count(/Root/Customers/Customer)', 'int');

;WITH rs AS
...