CODEWITHSUNDEEP
sqlsql-serverxml
Maxime Stor
Maxime Stor

Reputation: 41

Extract field from XML Data in SQL

I have an issue as I never done that before.

I have an SQL table with the following :

ID int;
xml_record xml;

The xml record is looking like that :

<root xml:space="preserve" id="XXX">
<c1>Data1</c1>
<c2>Data2</c2>
<c3>Data3</c3>
...
<cn>DataN</cn>
</root>

However I tried to use the following query with no success (return null) :

SELECT xml_record.value('c1[1]','varchar(50)') as value_c1
FROM myTable

The problem might come from the "space" but not sure.

Upvotes: 0

Views: 57

Answers (2)

Himanshu
Himanshu

Reputation: 3970

   SELECT 
   xml_record.value
   ( '(/root/c1/text())[1])', 
  'varchar(50)') as value_c1
   FROM myTable

else remove the first xml line

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272006

You just need to fix the expression:

SELECT xml_record.value('(/root/c1)[1]','varchar(50)') AS value_c1
FROM ...

Upvotes: 2

Related Questions