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 CustomerList
in a Sales
table that looks like something like below how do I extract the list of customers in sql?
假设我在一个销售表中有一个名为CustomerList的列,它看起来如下所示,那么如何用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>
2 个解决方案
#1
18
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
#2
19
You need to use CROSS APPLY from table to XML column
您需要使用从表到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 Customer
nodes anywhere in the XML at any level.
编辑—注意,上面的查询是快速编写的,用于说明如何处理表中的xml列(多行)。出于性能原因,不要使用'//Customer',而是使用绝对路径'/ArrayOfCustomers/Customer'。“//Customer”将遍历整个XML,在任何级别的XML中找到客户节点。
#1
18
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
#2
19
You need to use CROSS APPLY from table to XML column
您需要使用从表到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 Customer
nodes anywhere in the XML at any level.
编辑—注意,上面的查询是快速编写的,用于说明如何处理表中的xml列(多行)。出于性能原因,不要使用'//Customer',而是使用绝对路径'/ArrayOfCustomers/Customer'。“//Customer”将遍历整个XML,在任何级别的XML中找到客户节点。