How do you read XML column in SQL Server 2008?

Question

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?

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?

<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>

score 23 · Accepted Answer · answered Jan 27 '11 at 11:45

23

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

answered Jan 27 '11 at 11:45

marc_s

732,580
175
1,330
1,459

@user231465: yes, this solution works for a single XML - if you need to select from a whole table, then yes, you need the CROSS APPLY appoach. I would make sure to use `.nodes(/ArrayOfCustomers/Customer')` however - using the `//Customer` notation is handy, but terribly slow...... – marc_s Jan 27 '11 at 12:21

RichardTheKiwi · Answer 2 · 2011-01-27T12:37:23.723

You need to use CROSS APPLY from table to XML column

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.

Thanks for your reply.I seems to get some duplicate and not sure why — user9969, Jan 27 '11 at 12:16
This will work - but it's goint to be awfully slow, since you're using the `//Customer` notation - it's handy, but a terrible performer.... use `.nodes(/ArrayOfCustomers/Customer')` instead! Much faster..... — marc_s, Jan 27 '11 at 12:22

score -1 · Answer 3 · edited Sep 08 '20 at 07:40

-1

Try this query:

SELECT  AccountDetail.value('(/Accounts/Account/AccountNumber)[1]', 'varchar(100)') AS AccountNumber,
     AccountDetail.value('(/Accounts/Account/PayeeName)[1]', 'varchar(1000)') AS PayeeName,
     AccountDetail.value('(/Accounts/Account/Amount)[1]', 'decimal(20,2)') AS Amount
FROM [dbo].[AccountDetails]

edited Sep 08 '20 at 07:40

Merve Sahin

1,008
2
14
26

answered Sep 07 '20 at 16:33

naresh bhansali

1

How do you read XML column in SQL Server 2008?

3 Answers3

Your query:

Linked

Related