Using openXML in SQL Server 2012

Question

I'm trying to use the openXML function with SQL Server 2012 for the first time and I'm running into an issue. If I have a node that has no value i.e

<amenity id="bathtub" name="Bathtub" />

I'm always getting a NULL value returned when using the code below to extract the data from the XML. Any normal element ie

<name>Attic Loft in a historical building</name>

seems to work fine. Is there an easy way of checking for the existence of a node using openXML?

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM myXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT *
FROM OPENXML(@hDoc, 'properties/property/amenities')
WITH 
(
name [nvarchar](250) '../name',
externalId [nvarchar](50) '../id',
externalURL [nvarchar](250) '../landing_page_url',
description [nvarchar](max) '../description',
bathtub [bit] 'bathtub '
)

EXEC sp_xml_removedocument @hDoc
GO

marc_s · Accepted Answer · 2013-11-04 14:32:24Z

4

I would use the built-in, native XQuery support for SQL Server - OpenXML is old, clunky, leaks memory and so forth....

You can access attributes with the XPath expression using a @ indicator - something like this:

DECLARE @input XML = '<amenity id="bathtub" name="Bathtub" />'

SELECT
    ID = @input.value('(/amenity/@id)[1]', 'varchar(50)'),
    Name = @input.value('(/amenity/@name)[1]', 'varchar(50)')

Based on your XML input, you have two attributes - id and name (and nothing called bathtub as you seem to access in your OpenXML example....)

<amenity id="bathtub" name="Bathtub" />
         ^^           ^^^^
         *            * 
         *            * --> attribute is called "name"
         *
         *--> attribute is called "id" (not "bathtub" - that's it's *value*, not the name!)

For more information on XQuery support in SQL Server 2005 - read those articles:

Update: if you want to iterate over a list of XML elements, use the .nodes() function with a XPath expression to get a list of XML fragments which you can then grab individual bits from - something like this:

DECLARE @input XML = '<properties><property><amenity id="bathtub" name="Bathtub" /></property><property><amenity id="pool" name="Big honking pool" /></property></properties>'

SELECT
    ID = XCol.value('(amenity/@id)[1]', 'varchar(50)'),
    Name = XCol.value('(amenity/@name)[1]', 'varchar(50)')
FROM
    @input.nodes('/properties/property') AS XTbl(XCol)

edited Nov 4, 2013 at 14:32

answered Nov 4, 2013 at 13:52

marc_s

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

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

4 Comments

granadaCoder Over a year ago

agreed : OPENXML is old-school. "Move on up"!

James Privett Over a year ago

Damm, I thougth I was close. Thanks for the advice.

James Privett Over a year ago

Hi Marc, Can I use xquery to loop over a xml document and output the results like I was doing with openXML? I'm not great at SQL but I think your example would only give me row 1 of my document

marc_s Over a year ago

@JamesPrivett: sure! Use the .nodes() function with an XPath expression to get a list of XML fragments which you can then "shred" into their individual pieces. Read the links I've posted in my response to get a feel for how to do that. Updated my response with a sample

Collectives™ on Stack Overflow

Using openXML in SQL Server 2012

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related