iamsumesh iamsumesh - 3 months ago 19x
SQL Question

Iterate through XML variable in SQL Server

I have a XML variable in a stored procedure (SQL Server 2008), its sample value is


I have to take each category and insert into table as a separate record. How to iterate in XML and take individual node value?

If I want to call a stored procedure and send each category as input parameter, how we can do that? The stored procedure is legacy one, which accept only one category at at time. I am trying to do invoke procedure in this way.

  1. loop fetch single category from xml variable.

  2. invoke stored procedure with current category.

  3. move to next category.

  4. loop until list contain value.

Any help will be appreciated.


Something like this?

DECLARE @XmlVariable XML = '<parent_node>

INSERT INTO dbo.YourTargetTable(CategoryColumn)
     XTbl.Cats.value('.', 'varchar(50)')
     @XmlVariable.nodes('/parent_node/category') AS XTbl(Cats)

Update: if you must use the old legacy stored procedure and cannot change it (that would be my preferred way of doing this), then you would have to do the row-by-agonizing-row (RBAR) looping yourself, e.g. by using a table variable:

-- declare temporary work table
DECLARE @RbarTable TABLE (CategoryName VARCHAR(50))

-- insert values into temporary work table
INSERT INTO @RbarTable(CategoryName)
     XTbl.Cats.value('.', 'varchar(50)')
     @XmlVariable.nodes('/parent_node/category') AS XTbl(Cats)

-- declare a single category
DECLARE @CategoryNameToBeInserted VARCHAR(50)

-- get the first category
SELECT TOP 1 @CategoryNameToBeInserted = CategoryName FROM @RbarTable

-- as long as we have data
WHILE @CategoryNameToBeInserted IS NOT NULL
    -- execute your stored procedure here.....    
    EXEC sp_executesql N'dbo.YourStoredProcedure @CategoryName', 
                       N'@CategoryName VARCHAR(50)', 
                       @CategoryName = @CategoryNameToBeInserted

    -- delete the category we just inserted from the temporary work table
    DELETE FROM @RbarTable WHERE CategoryName = @CategoryNameToBeInserted

    -- see if we still have more categories to insert    
    SET @CategoryNameToBeInserted = NULL
    SELECT TOP 1 @CategoryNameToBeInserted = CategoryName FROM @RbarTable ORDER BY CategoryName