Getting Values From Xml Type Field Using In Sql Server

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

Solution 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

Solution 2:

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