在SQL Server中遍历XML变量

Question

我在存储过程（SQL Server 2008）中有一个XML变量，其示例值为

<parent_node>
   <category>Low</category>
   <category>Medium</category>
   <category>High</category>
</parent_node>

我必须采用每个类别并将其作为单独的记录插入表中。如何在XML中进行迭代并获取单个节点的值？

如果我想调用存储过程并将每个类别作为输入参数发送，我们该怎么做？该存储过程是旧式存储过程，一次只能接受一个类别。我正在尝试以这种方式调用过程。

1. 循环从xml变量获取单个类别。
2. 用当前类别调用存储过程。
3. 移至下一个类别。
4. 循环直到列表包含值。

任何帮助将不胜感激。

Answer 1

像这样吗？

DECLARE @XmlVariable XML = '<parent_node>
                              <category>Low</category>
                              <category>Medium</category>
                              <category>High</category>
                            </parent_node>'

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

更新： 如果您 必须
使用旧的旧式存储过程并且不能更改它（这是我的首选方式），那么您将不得不自己进行逐行行（RBAR）循环，例如通过使用一个表变量：

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

-- insert values into temporary work table
INSERT INTO @RbarTable(CategoryName)
  SELECT 
     XTbl.Cats.value('.', 'varchar(50)')
  FROM 
     @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
BEGIN
    -- 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
END