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?
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!
<linktype> and two <url> subnodes....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>
