inserting multiple rows via xml in sql server

Question

I am trying to insert data into a temporary table from a xml file, problem is that I can enter the data one row at a time from this:

DECLARE @tblCountry TABLE
(
CountryCode nvarchar(5),
CountryName nvarchar(50)
)
INSERT INTO @tblCountry(CountryCode, CountryName)
SELECT --DISTINCT
x.v.value('(/countries/country/@code)[1]','VARCHAR(5)'),
x.v.value('(/countries/country/text())[1]','VARCHAR(50)')
FROM @countryXML.nodes('/countries/country') x(v)
SELECT CountryCode, CountryName FROM @tblCountry

but when I am trying to put it in a loop I can not get it to work:

create proc spcountry
@count int
AS
BEGIN
DECLARE @countryXML XML
SET @countryXML = '<countries>
<country code="AF" iso="4">Afghanistan</country>
<country code="ZW" iso="716">Zimbabwe</country>
</countries>'
DECLARE @tblCountry TABLE
(
CountryCode nvarchar(5),
CountryName nvarchar(50)
)
SET @count = 1
WHILE (@count < 238)
BEGIN

INSERT INTO @tblCountry(CountryCode, CountryName)
SELECT --DISTINCT
x.v.value('(/countries/country/@code)[sql:variable("@count")]','VARCHAR(5)'),
x.v.value('(/countries/country/text())[[sql:variable("@count")]]','VARCHAR(50)')
FROM @countryXML.nodes('/countries/country') x(v)
set @count = @count + 1
END
END
SELECT CountryCode, CountryName FROM @tblCountry

I was following this link's solution: XML data type method “value” must be a string literal

Are you trying to use the loog to get the top @count rows? – Biz. Nigatu Jan 24 '18 at 22:24 — Biz. Nigatu, Jan 24 '18 at 22:24
No I have that many rows, so I counted and ran it till 238 – Ayush Bhargava Jan 25 '18 at 09:10 — Ayush Bhargava, Jan 25 '18 at 09:10

score 1 · Accepted Answer · answered Jan 24 '18 at 22:59

create proc spcountry @count int
AS
    declare @countryXML XML;
    declare @tblCountry TABLE
    (
        CountryCode nvarchar(5),
        CountryName nvarchar(50)
    );
begin

    SET @countryXML = '<countries>
    <country code="AF" iso="4">Afghanistan</country>
    <country code="ZW" iso="716">Zimbabwe</country>
    </countries>';


    INSERT INTO @tblCountry(CountryCode, CountryName)
    SELECT TOP(@count)
        x.v.value('@code','VARCHAR(50)'),
        x.v.value('.','VARCHAR(50)')
    FROM @countryXML.nodes('/countries/country') x(v)
    SELECT CountryCode, CountryName 
    FROM @tblCountry;

end
go

-- sample call
-- exec spcountry 2

inserting multiple rows via xml in sql server

1 Answers1