How to query xml field in table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

How to query xml field in table

Author

Topic

fralo
Posting Yak Master

161 Posts

Posted - 2014-04-24 : 09:36:41

Hi,

I googled first of course but could find no page which really helped me. In my table, Audit.Log, is contained a field called 'logdata'. The values appear like links in the table, which when clicked on reflects data like this:

<row UNIQUEKEY="3cGNR2zQ9ENx3iqUpe0hfI">
<column name="ReportStatus" old="9" new="7" />
</row>

I need to retrieve the values 'name','old','new', along with certain other fields within the record.

I would be most appreciative if someone could help me with this.

Thank you.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-24 : 10:13:19

See this example; you can copy and run this to an SSMS query window and run it to see what it does. I am assuming that you have multiple rows of data with multiple columns. If not, the query can be simplified a little bit.

DECLARE @x XML = '<row UNIQUEKEY="3cGNR2zQ9ENx3iqUpe0hfI">
<column name="ReportStatus" old="9" new="7" />
</row>
';

SELECT
	c2.value('./@name','varchar(32)') AS NAME,
	c2.value('./@old','varchar(32)') AS old,
	c2.value('./@new','varchar(32)') AS new
FROM
	@x.nodes('/row') T1(c1)
	CROSS APPLY c1.nodes('./column') T2(c2)

fralo
Posting Yak Master

161 Posts

Posted - 2014-04-24 : 10:36:07

Thanks James, but I have more than one row, not just the sample one I cited. So how does hardcoding it in the DECLARE help?

Also, in addition to these specific values (name, old, new) I will need to add to it certain other fields from within the table. Something like...

SELECT ID, REPDATE,
c2.value('./@name','varchar(32)') AS NAME,
c2.value('./@old','varchar(32)') AS old,
c2.value('./@new','varchar(32)') AS new
FROM
@x.nodes('/row') T1(c1)
CROSS APPLY c1.nodes('./column') T2(c2)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-24 : 11:03:35

Would be something like this. If you can post the DDL for the table and some sample data to populate the table, I can make the query more specific.

SELECT t0.ID, t0.REPDATE,
c2.value('./@name','varchar(32)') AS NAME,
c2.value('./@old','varchar(32)') AS old,
c2.value('./@new','varchar(32)') AS new
FROM
YourTable t0
CROSS APPLY t0.YourXMLColumn.nodes('/row') T1(c1)
CROSS APPLY c1.nodes('./column') T2(c2)

fralo
Posting Yak Master

161 Posts

Posted - 2014-04-24 : 11:36:24

Thanks James. I tailored your query to my table name and fields, and got it to work.

You were a tremendous help. Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources