Getting values from XML type field using in SQL server

Question

I have MS SQL table which contains a XML type field. This field has data in the format below:

<doc>
   <quote>
      <code>AA</code>
   </quote>
   <quote>
      <code>BB</code>
   </quote>
   <quote>
      <code>CC</code>
   </quote>
</doc>

The quotes can be in different orders. I need to see the data in the below format which shows which quote came first second and third for each document.

Code 1       Code 2        Code 3
--------------------------------
   AA         BB          CC
   BB         AA          CC

marc_s · Accepted Answer · 2011-09-26 16:57:35Z

1

Try this:

DECLARE @test TABLE(ID INT, XmlCol XML)

INSERT INTO @test VALUES(1, '<doc>
   <quote>
      <code>AA</code>
   </quote>
   <quote>
      <code>BB</code>
   </quote>
   <quote>
      <code>CC</code>
   </quote>
</doc>')

INSERT INTO @test VALUES(2, '<doc>
   <quote>
      <code>BB</code>
   </quote>
   <quote>
      <code>AA</code>
   </quote>
   <quote>
      <code>CC</code>
   </quote>
</doc>')

SELECT
    ID,
    X.Doc.value('(quote/code)[1]', 'varchar(20)') AS 'Code1',
    X.Doc.value('(quote/code)[2]', 'varchar(20)') AS 'Code2',
    X.Doc.value('(quote/code)[3]', 'varchar(20)') AS 'Code3'
FROM @test
CROSS APPLY xmlcol.nodes('doc') AS X(Doc)

Gives you an output of:

ID  Code1   Code2   Code3
1   AA  BB  CC
2   BB  AA  CC

answered Sep 26, 2011 at 16:57

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Comments

Kirill Polishchuk · Accepted Answer · 2011-09-26 17:03:12Z

0

declare @x xml = '<doc>
   <quote>
      <code>AA</code>
   </quote>
   <quote>
      <code>BB</code>
   </quote>
   <quote>
      <code>CC</code>
   </quote>
</doc>';

select @x.value('(doc/quote/code)[1]', 'varchar(max)')
    , @x.value('(doc/quote/code)[2]', 'varchar(max)')
    , @x.value('(doc/quote/code)[3]', 'varchar(max)')

For @marc_s,

DECLARE @test TABLE(ID INT, XmlCol XML)

INSERT INTO @test VALUES(1, '<doc>
   <quote>
      <code>AA</code>
   </quote>
   <quote>
      <code>BB</code>
   </quote>
   <quote>
      <code>CC</code>
   </quote>
</doc>')

INSERT INTO @test VALUES(2, '<doc>
   <quote>
      <code>BB</code>
   </quote>
   <quote>
      <code>AA</code>
   </quote>
   <quote>
      <code>CC</code>
   </quote>
</doc>')




SELECT
    ID,
    XmlCol.value('(doc/quote/code)[1]', 'varchar(20)') AS 'Code1',
    XmlCol.value('(doc/quote/code)[2]', 'varchar(20)') AS 'Code2',
    XmlCol.value('(doc/quote/code)[3]', 'varchar(20)') AS 'Code3'
FROM @test

edited Sep 26, 2011 at 17:03

answered Sep 26, 2011 at 16:55

Kirill Polishchuk

56.4k11 gold badges125 silver badges127 bronze badges

1 Comment

marc_s Over a year ago

OK, in this case, when you have a single <doc> node in your XML column - yes it works.

Collectives™ on Stack Overflow

Getting values from XML type field using in SQL server

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related