CODEWITHSUNDEEP
sql-serverxmlxquery
TallKewlOnez
TallKewlOnez

Reputation: 15

retrieving data from xml column sql

I have a table with an xml type column (column name is Before) here is a sample of the data

<row ID="47" By="Test User" Date="2006-07-26T00:00:00" Status="Closed" 
     Closed_Date="2012-06-22T11:14:35.237" Closed_By="MrBig" />

When I use this query to retrive the data, i get Nulls instead of the ID Numbers

SELECT    before.value('ID[1]', 'int') ItemId
FROM dbo.Audit
CROSS APPLY Before.nodes('.') N(C)

Where am I going wrong?

Upvotes: 1

Views: 1227

Answers (2)

Andomar
Andomar

Reputation: 238048

It's an attribute, so you have to prefix it with @, like @ID. An attribute must always be prefixed with a node, so the XPath query becomes /row/@ID. For example:

SELECT  Before.value('(/row/@ID)[1]', 'int') ItemId
FROM    Audit

As far as I can see, there's no need for a cross apply here.

Live example at SQL Fiddle.

Upvotes: 1

marc_s
marc_s

Reputation: 754258

You need to change it to this:

SELECT before.value('@ID[1]', 'int') ItemId
FROM @Audit
CROSS APPLY Before.nodes('/row') N(C)

Use .nodes('/row') to select the <row> node - and use @ID to select the XML attribute.

However, using this, you'll get an error:

Msg 2390, Level 16, State 1, Line 6
XQuery [@Audit.before.value()]: Top-level attribute nodes are not supported

Since you only have a single <row> element in your XML (that's correct, right?), you can use this instead:

SELECT 
    before.value('(row/@ID)[1]', 'int') ItemId
FROM dbo.Audit

Upvotes: 0

Related Questions