XML Fragment Parse

Question

Post reply

XML Fragment Parse

Jeff K

SSC-Addicted

Points: 417
More actions
February 24, 2010 at 3:48 pm

#141628

I have been assigned a task involving XML parsing and am stumped. Essentially, an XML fragment is passed into a stored procedure as a variable and I need to parse it to determine what actions are necessary. This is my first attempt at using any of SQL Server's XML functionality.
The code below is what I have found that should work, but I get NULL values returned rather than the value expected. I changed a.value to a.exist (and corrected syntax) and all returned 0. Am I missing something?
DECLARE @XML XML
SELECT @XML = '<table><column><columnid>1</columnid><columnname>COLOR_DESCR</columnname><aliasname>Description</aliasname><descr></descr></column></table>'
SELECT a.value('@columnid', 'VARCHAR(100)') AS ColumnId,
a.value('@columnname', 'VARCHAR(100)') AS ColumnName,
a.value('@aliasname', 'VARCHAR(100)') AS AliasName,
a.value('@descr', 'VARCHAR(100)') AS descr
FROM @XML.nodes('/table/column') TempXML (a);

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

When you extract elements you should use the element name (e.g. columnid[1]).

The way you wrote the code you're trying to query attributes.

Here are both versions for comparison and to show the difference:

DECLARE @XML XML

DECLARE @XML2 XML

SELECT @XML = '<table><column><columnid>1</columnid><columnname>COLOR_DESCR</columnname><aliasname>Description</aliasname><descr></descr></column></table>'

SELECT @XML2 = '<table><column columnid="1" columnname="COLOR_DESCR" aliasname="Description" descr=""/></table>'

SELECT a.value('columnid[1]', 'VARCHAR(100)') AS ColumnId,

a.value('columnname[1]', 'VARCHAR(100)') AS ColumnName,

a.value('aliasname[1]', 'VARCHAR(100)') AS AliasName,

a.value('descr[1]', 'VARCHAR(100)') AS descr

FROM @XML.nodes('/table/column') TempXML (a);

SELECT a.value('@columnid', 'VARCHAR(100)') AS ColumnId,

a.value('@columnname', 'VARCHAR(100)') AS ColumnName,

a.value('@aliasname', 'VARCHAR(100)') AS AliasName,

a.value('@descr', 'VARCHAR(100)') AS descr

FROM @XML2.nodes('/table/column') TempXML (a);

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff K SSC-Addicted Points: 417 More actions · Answer 2

Excellent!

I knew I had to be missing something, but could not figure out what it was.