Get Values ​​from an XML Column

Question

I have an XML column with this value

<sso_links> <linktype>mytype</linktype> <url>http://mydomain.com</url> <linktype>mytype2</linktype> <url>http://somedomain.com</url> </sso_links> 

I tried using SQL to query this value, but this only returns a scalar type

 SELECT XMLCOLUMN.value('//linktype[1]', varchar(max)), XMLCOLUMN.value('//url[1]', varchar(max)) from table where someid = '1' 

This created 2 lines from XML. How can I print all the values?

Answer 1

Create table

 CREATE TABLE docs (pk INT PRIMARY KEY, xCol XML not null) 

Insert values

 INSERT INTO docs VALUES (1, '<sso_links> <linktype>mytype</linktype> <url>http://mydomain.com</url> <linktype>mytype2</linktype> <url>http://somedomain.com</url> </sso_links>') 

Now select the desired values

 SELECT xCol.value('(/sso_links//linktype/node())[1]', 'nvarchar(max)') AS linktype ,xCol.value('(/sso_links//url/node())[1]', 'nvarchar(max)') AS url ,xCol.value('(/sso_links//linktype/node())[2]', 'nvarchar(max)') AS linktype2 ,xCol.value('(/sso_links//url/node())[2]', 'nvarchar(max)') AS url2 FROM docs WHERE pk = 1 

Give it a try!

Answer 2

If you need a “generic” version that will analyze all trays under <sso_links> and create their name and value, use something like this:

 SELECT NodeName = node.value('local-name(.)', 'varchar(100)'), NodeValue = node.value('(.)[1]', 'varchar(200)') FROM dbo.YourTable CROSS APPLY XmlColumn.nodes('/sso_links/*') AS links(node) WHERE SomeID = 1 

For your XML sample, I get the output:

 NodeName NodeValue linktype mytype url http://mydomain.com linktype mytype2 url http://somedomain.com 

Since it does not use specific node names or indexes, this will work for any number of sub-nodes in <sso_links>