Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do you read XML column in SQL Server 2008?

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>
like image 265
user9969 Avatar asked Jan 27 '11 11:01

user9969


People also ask

How do I get data from XML format in SQL Server?

SQL Server lets you retrieve data as XML by supporting the FOR XML clause, which can be included as part of your query. You can use the FOR XML clause in the main (outer) query as well as in subqueries. The clause supports numerous options that let you define the format of the XML data.


Video Answer


1 Answers

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
like image 194
marc_s Avatar answered Oct 06 '22 02:10

marc_s