遍历 SQL Server 中的 XML 变量 [英] Iterate through XML variable in SQL Server

问题描述

我在存储过程(SQL Server 2008)中有一个 XML 变量，它的示例值为

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

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

我必须将每个类别作为单独的记录插入表中.如何在 XML 中迭代并获取单个节点值?

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. 循环从 xml 变量中获取单个类别.
2. 使用当前类别调用存储过程.
3. 移至下一个类别.
4. 循环直到列表包含值.

任何帮助将不胜感激.

推荐答案

类似的事情?

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)

更新:如果您必须使用旧的遗留存储过程并且无法更改它(这将是我的首选方式)，那么您将不得不这样做row-by-agonizing-row (RBAR) 循环自己，例如通过使用表变量:

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

这篇关于遍历 SQL Server 中的 XML 变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！