通过SQL Server中的XML迭代变量(Iterate through XML variable

我有一个存储过程（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

Answer 2:

在SQL Server中的XML总是有这样做的方法不止一种。 根据您的XML文档的大小和要查询它的次数，你可能是最好关闭使用sp_xml_preparedocument其解析文档，为您提供了一个句柄引用它，然后你就可以查询它多次和途径只要你想。 这里是你如何做到这一点：

declare @xml xml = '
<parent_node>
   <category>Low</category>
   <category>Medium</category>
   <category>High</category>
</parent_node>'
declare @xml_handle int

exec sp_xml_preparedocument @xml_handle output, @xml

select value from openxml(@xml_handle, '/parent_node/category', 2) with (value varchar(100) 'text()') x

exec sp_xml_removedocument @xml_handle

文章来源: Iterate through XML variable in SQL Server