CODEWITHSUNDEEP
sql-server-2008
Ryan Mathes
Ryan Mathes

Reputation: 63

Get values from XML column

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've tried using SQL to query this by value but this only returns scalar type

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

This produced 2 rows from the XML. How can I output all values?

Upvotes: 4

Views: 476

Answers (2)

marc_s
marc_s

Reputation: 754268

If you want a "generic" version that will parse all subnodes under <sso_links> and produce 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 sample XML, I get an output of:

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

Since it uses no specific node names or indexes, this will work for any number of subnodes under <sso_links>

Upvotes: 1

Rajan
Rajan

Reputation: 198

Create table

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

Insert the 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 values you want

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!

Upvotes: 1

Related Questions