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