CODEWITHSUNDEEP
sql-serverxml
Santu
Santu

Reputation: 121

Getting XML value in SQL query

I have a column in a SQL Server table which is of datatype XML. I am trying to query an element from that XML column, but I'm failing.

The XML and the query is below. Appreciate if someone can help.

<?xml version="1.0" encoding="UTF-8"?>
<ArrayOfDataValue xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <DataValue>
      <FieldValue>Activity</FieldValue>
      <FirstOutput xsi:type="xsd:string">Play</FirstOutput>
      <SecondOutput xsi:type="xsd:string">Sleep</SecondOutput>
   </DataValue>
   <DataValue>
      <FieldValue>Count</FieldValue>
      <FirstOutput xsi:type="xsd:string">21</FirstOutput>
      <SecondOutput xsi:type="xsd:string">25</SecondOutput>
   </DataValue>
   <DataValue>
      <FieldValue>Area</FieldValue>
      <FirstOutput xsi:type="xsd:string">City</FirstOutput>
      <SecondOutput xsi:type="xsd:string">Country</SecondOutput>
   </DataValue>
</ArrayOfDataValue>

Query:

Select 
    [xmlfield].value('(/DataValue//FirstOutput/node())[1]', 'nvarchar(max)') as FieldName

The output I am looking for is to get the Values of FieldValue, FirstOutput and SecondOutput

Example:

FirstOutput
Play

It is a SQL Server database

Upvotes: 1

Views: 842

Answers (1)

marc_s
marc_s

Reputation: 754258

Try something like this:

SELECT
    FieldValue = XC.value('(FieldValue)[1]', 'varchar(50)'),
    FirstOutput = XC.value('(FirstOutput)[1]', 'varchar(50)'),
    SecondOutput = XC.value('(SecondOutput)[1]', 'varchar(50)')
FROM
    dbo.YourTableNameHere
CROSS APPLY
    XmlColumn.nodes('/ArrayOfDataValue/DataValue') AS XT(XC)

Replace dbo.YourTableNameHere with your actual table name, and XmlColumn with the name of your XML column.

You should get an output something like:

enter image description here

Upvotes: 3

Related Questions