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
– marc_s, Commented Nov 22, 2018 at 20:45

Salman Arshad · Accepted Answer · 2018-11-22 19:47:46Z

2

You just need to fix the expression:

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

answered Nov 22, 2018 at 19:47

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Himanshu · Accepted Answer · 2018-11-23 08:35:31Z

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, 2018 at 8:35

answered Nov 22, 2018 at 19:28

Himanshu

3,9802 gold badges15 silver badges34 bronze badges

3 Comments

Maxime Stor Over a year ago

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 ?

Gottfried Lesigang Over a year ago

@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()

Himanshu Over a year ago

@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 :)

Collectives™ on Stack Overflow

Extract field from XML Data in SQL

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related