将XML节点插入到SQL Server中的表中

I want to get an XML data and insert each node value in a row of a table. For example I have:

我想获取XML数据并将每个节点值插入到表的一行中。例如我有:

<Tags> 
    <Tag>a</Tag> 
    <Tag>b</Tag> 
    <Tag>c</Tag>
</Tags>

and I want to insert a, b and c in a table named Tags. How should I do this?

我想在一个名为Tags的表中插入ab c。我该怎么做呢?

I have this code so far but I don't know how to define @I in the xquery part. By the way is there any simpler way?

到目前为止，我已经有了这段代码，但是我不知道如何在xquery部分定义@I。顺便问一下，还有更简单的方法吗?

CREATE TABLE #T (tag nvarchar(100))

DECLARE @TagsXML xml
DECLARE @TagsCount int
DECLARE @I int = 0

SET @TagsXML = '<Tags><Tag>a</Tag><Tag>b</Tag><Tag>c</Tag></Tags>'
SET @TagsCount = (SELECT T.x.value('count(Tag)', 'nvarchar(100)') FROM @TagsXML.nodes('Tags') T(x))

WHILE @I < @TagsCount
BEGIN
    INSERT INTO #T VALUES ((SELECT T.x.value('concat("Tag[", @I, "]")', 'nvarchar(100)') FROM @TagsXML.nodes('Tags') T(x)))
    SET @I = @I + 1
END

SELECT * FROM #T

1 个解决方案

#1

Don't use WHILE loops - think in sets!

不要使用WHILE循环——在集合中思考!

You can do this easily in a single statement:

你可以在一句话中很容易地做到:

INSERT INTO #T(tag)
    SELECT
        XTags.value('.', 'nvarchar(100)')
    FROM 
        @TagsXML.nodes('/Tags/Tag') AS XTbl(XTags)

The call to .nodes() gives you a list of the XML tags that match this XPath expression (as XML fragments) - here, you get a list of all <tag> elements. And from that list of XML fragments, I pick the only element's contents as nvarchar(100) and insert it into the table. Much more efficient and much better performing than a RBAR (row-by-agonizing-row) approach with a WHILE loop

对.nodes()的调用提供了与此XPath表达式(作为XML片段)匹配的XML标记的列表——在这里，您将得到所有元素的列表。从XML片段列表中，我选择唯一元素的内容为nvarchar(100)并将其插入到表中。使用WHILE循环时，比RBAR(逐行逐行)方法更有效，也更好。

#1