在 SQL Server 2008 中如何读取 XML 列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4815836/
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
How do you read XML column in SQL Server 2008?
提问by user9969
I have never used XML in SQL Server 2008, I need to extract a list of customers into a variable table how do you do it?
我从来没有在 SQL Server 2008 中使用过 XML，我需要将客户列表提取到一个变量表中，你怎么做？
Given that I have a column called CustomerListin a Salestable that looks like something like below how do I extract the list of customers in sql?
鉴于我CustomerList在Sales表中调用了一个列，它看起来像下面这样，如何在 sql 中提取客户列表？
<ArrayOfCustomers xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Customer>
<ItemId>1</ItemId>
<Value>Mr Smith</Value>
</Customer>
<Customer>
<ItemId>2</ItemId>
<Value>Mr Bloggs</Value>
</Customer>
</ArrayOfCustomers>
回答by marc_s
Try something like this:
尝试这样的事情：
SELECT
Cust.value('(ItemId)[1]', 'int') AS 'ItemID',
Cust.value('(Value)[1]', 'Varchar(50)') AS 'Customer Name'
FROM
dbo.Sales.CustomerList.nodes('/ArrayOfCustomers/Customer') AS AOC(Cust)
That should give you an output something like this:
这应该给你一个输出是这样的：
ItemID Customer Name
1 Mr Smith
2 Mr Bloggs
回答by RichardTheKiwi
You need to use CROSS APPLY from table to XML column
您需要使用 CROSS APPLY 从表到 XML 列
create table sales (customerlist xml)
insert sales select '
<ArrayOfCustomers xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Customer>
<ItemId>1</ItemId>
<Value>Mr Smith</Value>
</Customer>
<Customer>
<ItemId>2</ItemId>
<Value>Mr Bloggs</Value>
</Customer>
</ArrayOfCustomers>'
Your query:
您的查询：
SELECT
N.C.value('ItemId[1]', 'int') ItemId,
N.C.value('Value[1]', 'varchar(100)') Value
FROM dbo.Sales
CROSS APPLY CustomerList.nodes('//Customer') N(C)
EDIT - note
The query above was written quickly to illustrate working with xml columns in a table (multi-row). For performance reasons, don't use '//Customer' but use an absolute path instead '/ArrayOfCustomers/Customer'. '//Customer' will go through the entire XML to find Customernodes anywhere in the XML at any level.
编辑 - 注意
上面的查询是快速编写的，以说明如何使用表（多行）中的 xml 列。出于性能原因，不要使用“//Customer”，而是使用绝对路径而不是“/ArrayOfCustomers/Customer”。'//Customer' 将遍历整个 XML 以Customer在任何级别的 XML 中的任何位置查找节点。
