Querying XML Rows For New Non-XML Rows

sql-server, xml

Solution

Do it like this:

SELECT  nodes.child.value('@key','varchar(100)')
FROM    ##XML a
        CROSS APPLY a.value.nodes('/Animals') AS nodes(child)

Problem

Note: See this spelled out example on SQL Fiddle, or look at the code below:, as the `DECLARE @XML variable` examples are confusing the syntax when trying to actually obtain XML data from an XML column and intended for non-XML rows: ``` CREATE TABLE ##xml ( ID TINYINT IDENTITY(1,1), Value XML ) INSERT INTO ##xml (Value) VALUES ('<Animals key="zoo" fish="22" dogs="0" birds="4" />') , ('<Animals key="house" fish="0" dogs="1" birds="2" />') , ('<Animals key="business" fish="0" dogs="0" birds="12" />') SELECT * FROM ##xml SELECT nodes.child.value('key[1]', 'VARCHAR(50)') FROM ##xml.Value.nodes('Animal') AS nodes(child) -- Errors here, though the syntax looks correct DROP TABLE ##xml ``` I'm getting the error, "Invalid column name '##xml'. The XMLDT method 'nodes' can only be invoked on columns of type xml" even though I'm trying to query the nodes of XML to produce a result like the below: ``` Key | Fish | Dogs | Birds Zoo 22 0 4 House 0 1 2 Business 0 0 12 ``` Note, if I change the syntax of the FROM statement to point to the specific column (Value), I receive other errors about Value not being a recognized built-in function name.

Original source