Retrieving Values ​​from an XML Type Field Used on SQL Server

Question

I have an MS SQL table that contains an XML type field. This field contains data in the following format:

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

Quotation marks can be in different orders. I need to see the data in the following format, which shows which quote was first and third for each document.

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

Answer 1

Try the following:

 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 output:

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

Answer 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