SQL：如何获取 XML 数据类型中的属性值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8808652/
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: How can i get the value of an attribute in XML datatype
提问by ThdK
I have the following xml in my database:
我的数据库中有以下 xml：
<email>
<account language="en" ... />
</email>
I'm using something like this now: but still have to find the attribute value
我现在正在使用这样的东西：但仍然必须找到属性值
SELECT
convert(xml,m.Body).query('/Email/Account')
FROM Mail
How can i get the value of the languageattribute in my select statement with SQL?
如何language使用 SQL 在我的选择语句中获取属性的值？
回答by Kirill Polishchuk
Use XQuery:
使用 XQuery：
declare @xml xml =
'<email>
<account language="en" />
</email>'
select @xml.value('(/email/account/@language)[1]', 'nvarchar(max)')
declare @t table (m xml)
insert @t values
('<email><account language="en" /></email>'),
('<email><account language="fr" /></email>')
select m.value('(/email/account/@language)[1]', 'nvarchar(max)')
from @t
Output:
输出：
en
fr
回答by Bassam Mehanni
This should work:
这应该有效：
DECLARE @xml XML
SET @xml = N'<email><account language="en" /></email>'
SELECT T.C.value('@language', 'nvarchar(100)')
FROM @xml.nodes('email/account') T(C)
回答by Yuck
It depends a lot on how you're querying the document. You can do this, though:
这在很大程度上取决于您查询文档的方式。不过，您可以这样做：
CREATE TABLE #example (
document NText
);
INSERT INTO #example (document)
SELECT N'<email><account language="en" /></email>';
WITH XmlExample AS (
SELECT CONVERT(XML, document) doc
FROM #example
)
SELECT
C.value('@language', 'VarChar(2)') lang
FROM XmlExample CROSS APPLY
XmlExample.doc.nodes('//account') X(C);
DROP TABLE #example;
EDITafter changes to your question.
在更改您的问题后进行编辑。
