SQL 选择 XML 节点作为行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/192398/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select XML nodes as rows
提问by Bj?rn
I am selecting from a table that has an XML column using T-SQL. I would like to select a certain type of node and have a row created for each one.
我正在使用 T-SQL 从具有 XML 列的表中进行选择。我想选择某种类型的节点并为每个节点创建一行。
For instance, suppose I am selecting from a peopletable. This table has an XML column for addresses. The XML is formated similar to the following:
例如，假设我正在从people表中进行选择。该表有一个用于地址的 XML 列。XML 的格式类似于以下内容：
<address>
<street>Street 1</street>
<city>City 1</city>
<state>State 1</state>
<zipcode>Zip Code 1</zipcode>
</address>
<address>
<street>Street 2</street>
<city>City 2</city>
<state>State 2</state>
<zipcode>Zip Code 2</zipcode>
</address>
How can I get results like this:
我怎样才能得到这样的结果：
Name City State
名称 城市 州
Joe Baker Seattle WA
乔贝克西雅图华盛顿
Joe Baker Tacoma WA
乔贝克塔科马 WA
Fred Jones Vancouver BC
Fred Jones 温哥华 BC
回答by leoinfo
Here is your solution:
这是您的解决方案：
/* TEST TABLE */
DECLARE @PEOPLE AS TABLE ([Name] VARCHAR(20), [Address] XML )
INSERT INTO @PEOPLE SELECT
'Joel',
'<address>
<street>Street 1</street>
<city>City 1</city>
<state>State 1</state>
<zipcode>Zip Code 1</zipcode>
</address>
<address>
<street>Street 2</street>
<city>City 2</city>
<state>State 2</state>
<zipcode>Zip Code 2</zipcode>
</address>'
UNION ALL SELECT
'Kim',
'<address>
<street>Street 3</street>
<city>City 3</city>
<state>State 3</state>
<zipcode>Zip Code 3</zipcode>
</address>'
SELECT * FROM @PEOPLE
-- BUILD XML
DECLARE @x XML
SELECT @x =
( SELECT
[Name]
, [Address].query('
for $a in //address
return <address
street="{$a/street}"
city="{$a/city}"
state="{$a/state}"
zipcode="{$a/zipcode}"
/>
')
FROM @PEOPLE AS people
FOR XML AUTO
)
-- RESULTS
SELECT [Name] = T.Item.value('../@Name', 'varchar(20)'),
street = T.Item.value('@street' , 'varchar(20)'),
city = T.Item.value('@city' , 'varchar(20)'),
state = T.Item.value('@state' , 'varchar(20)'),
zipcode = T.Item.value('@zipcode', 'varchar(20)')
FROM @x.nodes('//people/address') AS T(Item)
/* OUTPUT*/
Name | street | city | state | zipcode
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Joel | Street 1 | City 1 | State 1 | Zip Code 1
Joel | Street 2 | City 2 | State 2 | Zip Code 2
Kim | Street 3 | City 3 | State 3 | Zip Code 3
回答by 6eorge Jetson
Here's how I do it generically:
这是我一般的做法：
I shred the source XML via a call such as
我通过一个调用粉碎了源 XML，例如
DECLARE @xmlEntityList xml
SET @xmlEntityList =
'
<ArbitrarilyNamedXmlListElement>
<ArbitrarilyNamedXmlItemElement><SomeVeryImportantInteger>1</SomeVeryImportantInteger></ArbitrarilyNamedXmlItemElement>
<ArbitrarilyNamedXmlItemElement><SomeVeryImportantInteger>2</SomeVeryImportantInteger></ArbitrarilyNamedXmlItemElement>
<ArbitrarilyNamedXmlItemElement><SomeVeryImportantInteger>3</SomeVeryImportantInteger></ArbitrarilyNamedXmlItemElement>
</ArbitrarilyNamedXmlListElement>
'
DECLARE @tblEntityList TABLE(
SomeVeryImportantInteger int
)
INSERT @tblEntityList(SomeVeryImportantInteger)
SELECT
XmlItem.query('//SomeVeryImportantInteger[1]').value('.','int') as SomeVeryImportantInteger
FROM
[dbo].[tvfShredGetOneColumnedTableOfXmlItems] (@xmlEntityList)
by utilizing the scalar-valued function
通过利用标量值函数
/* Example Inputs */
/*
DECLARE @xmlListFormat xml
SET @xmlListFormat =
'
<ArbitrarilyNamedXmlListElement>
<ArbitrarilyNamedXmlItemElement>004421UB7</ArbitrarilyNamedXmlItemElement>
<ArbitrarilyNamedXmlItemElement>59020UH24</ArbitrarilyNamedXmlItemElement>
<ArbitrarilyNamedXmlItemElement>542514NA8</ArbitrarilyNamedXmlItemElement>
</ArbitrarilyNamedXmlListElement>
'
declare @tblResults TABLE
(
XmlItem xml
)
*/
-- =============================================
-- Author: 6eorge Jetson
-- Create date: 01/02/3003
-- Description: Shreds a list of XML items conforming to
-- the expected generic @xmlListFormat
-- =============================================
CREATE FUNCTION [dbo].[tvfShredGetOneColumnedTableOfXmlItems]
(
-- Add the parameters for the function here
@xmlListFormat xml
)
RETURNS
@tblResults TABLE
(
-- Add the column definitions for the TABLE variable here
XmlItem xml
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
INSERT @tblResults
SELECT
tblShredded.colXmlItem.query('.') as XmlItem
FROM
@xmlListFormat.nodes('/child::*/child::*') as tblShredded(colXmlItem)
RETURN
END
--SELECT * FROM @tblResults
回答by Tao
In case this is useful to anyone else out there looking for a "generic" solution, I created a CLR procedure that can take an Xml fragment as above and "shred" it into a tabular resultset, without you providing any additional information about the names or types of the columns, or customizing your call in any way for the given Xml fragment:
如果这对寻找“通用”解决方案的其他人有用，我创建了一个 CLR 过程，该过程可以采用上述 Xml 片段并将其“切碎”为表格结果集，而无需您提供有关名称的任何其他信息或列的类型，或以任何方式为给定的 Xml 片段自定义您的调用：
http://architectshack.com/ClrXmlShredder.ashx
http://architectshack.com/ClrXmlShredder.ashx
There are of course some restrictions (the xml must be "tabular" in nature like this sample, the first row needs to contain all the elements/columns that will be supported, etc) - but I do hope it's a few steps ahead of what's available built-in.
当然有一些限制（xml本质上必须是“表格”，就像这个示例一样，第一行需要包含将支持的所有元素/列等） - 但我确实希望它比什么提前几步可用内置。
回答by JohnLBevan
Here's an alternate solution:
这是一个替代解决方案：
;with cte as
(
select id, name, addresses, addresses.value('count(/address/city)','int') cnt
from @demo
)
, cte2 as
(
select id, name, addresses, addresses.value('((/address/city)[sql:column("cnt")])[1]','nvarchar(256)') city, cnt-1 idx
from cte
where cnt > 0
union all
select cte.id, cte.name, cte.addresses, cte.addresses.value('((/address/city)[sql:column("cte2.idx")])[1]','nvarchar(256)'), cte2.idx-1
from cte2
inner join cte on cte.id = cte2.id and cte2.idx > 0
)
select id, name, city
from cte2
order by id, city
FYI: I've posted another version of this SQL on the code review site here: https://codereview.stackexchange.com/questions/108805/select-field-in-an-xml-column-where-both-xml-and-table-contain-multiple-matches
仅供参考：我已经在代码网站上发布了此 SQL 的另一个版本：https: //codereview.stackexchange.com/questions/108805/select-field-in-an-xml-column-where-both-xml- and-table-contain-multiple-matches
回答by Wyatt
If you can use it, the linq api is convenient for XML:
如果能用的话，linq api对XML很方便：
var addresses = dataContext.People.Addresses
.Elements("address")
.Select(address => new {
street = address.Element("street").Value,
city = address.Element("city").Value,
state = address.Element("state").Value,
zipcode = address.Element("zipcode").Value,
});
