Querying attributes and node values from XML stored in an XML data type

Question

I have written an interface to interact with the CubeIQ BlackBox (www.magiclogic.com) and I have an SQL Server 2005 database table where I store some regular text fields, dates, and two pieces of XML in columns that have an XML data type.

There are a few values I want to pick out of the XML, and I cannot figure out how to query the XML, or the attributes of nodes within that XML.

Here is the type of query I want to do:

 SELECT 
    [regular_table_field1],
    [regular_table_field2],
    [opt_resposne_xml].rootnode.wrapper.child@attr('someattribute') AS 'someattribute',
    [opt_resposne_xml].rootnode.wrapper.child@attr('someattribute2') AS 'someattribute2'
 FROM [optimizations] WHERE [opt_concept_id] = '1234'

marc_s · Accepted Answer · 2011-06-01 20:30:25Z

2

Not knowing what your XML looks like, here's a general purpose piece of code to give you an idea of what it would look like:

 SELECT 
    [regular_table_field1],
    [regular_table_field2],
    [opt_resposne_xml].value('(/rootnode/wrapper/child/@someattribute)[1]', 'int') AS 'someattribute',
    [opt_resposne_xml].value('(/rootnode/wrapper/child/@someattribute2)[1]', 'varchar(50)') AS 'someattribute2'
 FROM 
     [optimizations] 
 WHERE 
     [opt_concept_id] = '1234'

If you need to "reach into" the XML column and grab out a single value (from an XML element or attribute), you basically need to define the XPath to that location of where the information you're interested in is located, and you need to convert that to a given SQL Server datatype.

If you need to have a single relational row cross joined against a number of elements from a single XML field, you might need other approaches (CROSS APPLY).

Also: you might need to pay attention to XML namespaces that are involved - a common pitfall when trying to parse XML inside SQL Server.

answered Jun 1, 2011 at 20:30

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DirtyBirdNJ Over a year ago

Thank you so VERY much. I found the MSDN articles that explained how the process worked but could not get the syntax for the queries to work properly. Can you please explain why you had to put the XPath query in (parens) and why there's a [1] at the end of it? Is this the way of specifying the first, second third, etc appearance of that attribute in a set of nodes?

marc_s Over a year ago

Why parens? I don't know - you'd have to ask the Microsoft designers who defined this. The [1] denotes the first occurence - if you want to convert it to a single data value, you need to make sure it's just a single XML node / attribute being returned. And yes - you could also use [2] to get the second - but be careful to be sure there is a second one....

DirtyBirdNJ Over a year ago

I was actually trying this out to see how it would work... if I leave off the [1] I get an error... because there is no [2] it returns NULL if I use that. I was basically writing this exact same query but without the extra parens and the bracketed index indicator.

Collectives™ on Stack Overflow

Querying attributes and node values from XML stored in an XML data type

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related