Extract field from XML Data in SQL

Question

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.

XML support is **highly vendor-specific** - so please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Nov 22 '18 at 20:45

score 2 · Answer 1 · answered Nov 22 '18 at 19:47

2

You just need to fix the expression:

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

answered Nov 22 '18 at 19:47

Salman A

Himanshu · Answer 2 · 2018-11-23T08:35:31.960

0

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

else remove the first xml line

edited Nov 23 '18 at 08:35

answered Nov 22 '18 at 19:28

Himanshu

Thank you I'll try as soon as I can. But actually the record is a big line. How could I get rid of the first line ? – Maxime Stor Nov 22 '18 at 19:31
@HimanshuAhuja Why do you double the slash before `//c1`? This will trigger a *deep search*, which is not needed here... Furthermore you should pick [`text()` instead of `node()`](https://stackoverflow.com/a/11744783/5089204) – Shnugo Nov 23 '18 at 08:10
@Shnugo my bad actually i referred from a different article got your point now `//c1 ` will trigger for all c1s as you correctly said as deep search and wasnt aware that ` `text()` was there to select text inside the tag. thanks for the help :) – Himanshu Nov 23 '18 at 08:34

2 Answers2