tsql如何拆分xml并将它们作为行插入 [英] tsql how to split xml and insert them as rows
问题描述
这里有一个问题(如何使用 t-sql 更新 xml 变量中所有 xml 属性的值?)提问者想要将 xml 变量拆分为行.
There was a question here (How to update all xml attributes' value in an xml variable using t-sql?) where the questioner wanted to split an xml variable into rows.
我遇到了几乎相同的问题,但我将 xml 存储在表中.每行至少存储 1 个 xml 节点,有时更多.我想将它们拆分为单独的行,但似乎 .nodes('a') 需要一个标量变量.
I have almost the same problem, but I'm storing the xmls in a table. Every row stores at least 1 xml node, sometimes more. I want to split them to separate rows, but it seems like .nodes('a') needs a scalar variable.
这是我试过的:
declare @T table (XMLCol xml);
insert into @T values
('<a abb="122">
<b></b>
</a>
<a abb="144">
<b></b>
</a>'),
('<a abb="222">
<b></b>
</a>
<a abb="244">
<b></b>
</a>');
select a.query('.') from @T.nodes('a') a(a);
我想要达到的目标:我有一张这样的桌子:
What I want to achieve: I have a table like this:
ID XML
1 <a abb="122"><b></b></a><a abb="144"><b></b></a>
2 <a abb="222"><b></b></a><a abb="244"><b></b></a>
我想把它转换成这样:
ID XML
1 <a abb="122"><b></b></a>
2 <a abb="144"><b></b></a>
3 <a abb="222"><b></b></a>
4 <a abb="244"><b></b></a>
为此,我想写一些像简单的东西
For that I want to write something that works like a simple
INSERT INTO table2 SELECT * FROM table1
你能帮我吗?
提前致谢!
推荐答案
试试这个(如果我正确理解了你的问题):
Try like this (if I understood your question correctly):
declare @xml xml = N'<a abb="122"><b></b></a><a abb="144"><b></b></a>'
select @xml.query('/a/*')
如果您希望每个 b 节点都显示为行,请使用:
If you want each b node to be shown as row, use this:
select x.items.query('.') from @xml.nodes('/a/b') as x(items)
您可以像这样从表变量中获取数据:
You can get data from your table variable like this:
select tbl.vals
from @T as t
cross apply (select x.items.query('.') from t.XMLCol.nodes('/a') as x(items)) as tbl(vals)
这篇关于tsql如何拆分xml并将它们作为行插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!