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 ...