CODEWITHSUNDEEP
sql-serversql-server-2012openxml
James Privett
James Privett

Reputation: 1109

Using openXML in SQL Server 2012

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

Upvotes: 2

Views: 3879

Answers (1)

marc_s
marc_s

Reputation: 754488

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)

Upvotes: 4

Related Questions