SQL Server：如何在查询中获取 XML 属性？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/869405/
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
SQL Server: How to get hold of XML attributes in a query?
提问by marc_s
I'm currently trying to get all of the attributes from some XML with an SQL query.
我目前正在尝试使用 SQL 查询从某些 XML 中获取所有属性。
I've been attempting the following to retrieve it, but I must be missing something rather fundamental.
我一直在尝试以下方法来检索它，但我一定遗漏了一些相当基本的东西。
DECLARE @T varchar(max)
SET @T =
'<root>
<Field FieldRowId="1000">
<Items>
<Item Name="CODE"/>
<Item Name="DATE"/>
</Items>
<Attributes>
<Attribute ID ="1"/>
</Attributes>
</Field>
<Field FieldRowId="2000">
<Items>
<Item Name="CODE"/>
<Item Name="DATE"/>
</Items>
<Attributes>
<Attribute ID ="2"/>
</Attributes>
</Field>
</root>'
DECLARE @X xml
SET @X = CAST(@T as xml)
SELECT Y.ID.value('@FieldRowId', 'int') as FieldID,
Y.ID.value('/Items/@Name', 'varchar(max)') as "Name",
Y.ID.value('/Attributes/@ID', 'int') as AttributeID
FROM @X.nodes('/root/Field') as Y(ID)
回答by marc_s
You would have to try something like this: (the @name attribute is on the "Item" element - not the "Items" !)
您将不得不尝试这样的事情：（@name 属性位于“Item”元素上 - 而不是“Items”！）
SET @X = CAST(@T as xml)
SELECT
Y.ID.value('(@FieldRowId)[1]', 'int') as FieldID,
Y.ID.value('(Items/Item/@Name)[1]', 'varchar(max)') as "Name",
Y.ID.value('(Attributes/Attribute/@ID)[1]', 'int') as AttributeID
FROM @X.nodes('/root/Field') as Y(ID)
Marc
马克
回答by Sergio Guerrero
SELECT t1.fieldId, name, attributeId
FROM ( SELECT Y.ID.value('../../@FieldRowId', 'int') as FieldID
, Y.ID.value('@Name', 'varchar(max)') as Name
FROM @T.nodes('(/root/Field/Items/Item)') as Y(ID)
) t1 -- alias the first result
JOIN
(SELECT Y.ID.value('../../@FieldRowId', 'int') as FieldID
, Y.ID.value('@ID', 'int') as AttributeID
FROM @T.nodes('(/root/Field/Attributes/Attribute)') as Y(ID)
) t2 -- alias the second result
on t1.fieldid = t2.FieldID -- join them on a common column
