Help on querying strings XML column

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 2008 Forums

Transact-SQL (2008)

Help on querying strings XML column

Author

Topic

zencid
Starting Member

1 Post

Posted - 2011-07-12 : 15:56:16

Hi,

Our database has one table with unstructured XML data stored as type Ntext. I have found it is pretty useless in this form. I was able to successfully copy this data out into a different table or convert it on the fly to data type XML so it can be more useful. However I am now stuck trying to pull bits of information out of the strings that I want without further porting into another table or variable.

Any ideas on how I could say pull any lines with "Operation Timed Out"

One row in the column looks like this example
<summary />
<errors>
<error date="1306349726">
<detail id="3363983169" />
</error>
</errors>
<warnings />
<diagnostic>
<message date="1306349203" file="" line="">
<detail id="1195383211">
<arg type="0">The agent is about to bounce/reconnect.</arg>
</detail>
</message>
<message date="1306349233" file="" line="">
<detail id="1195383211">
<arg type="0">The agent is about to bounce/reconnect to server: BadKarma.supp.englab.local</arg>
</detail>
</message>
<message date="1306349338" file="" line="">
<detail id="1195383211">
<arg type="0">The agent is about to bounce/reconnect to server: BadKarma.supp.englab.local</arg>
</detail>
</message>
<message date="1306349442" file="" line="">
<detail id="1195383211">
<arg type="0">The agent is about to bounce/reconnect to server: BadKarma.supp.englab.local</arg>
</detail>
</message>
<message date="1306349547" file="" line="">
<detail id="1195383211">
<arg type="0">The agent is about to bounce/reconnect to server: BadKarma.supp.englab.local</arg>
</detail>
</message>
<message date="1306349651" file="" line="">
<detail id="1195383211">
<arg type="0">The agent is about to bounce/reconnect to server: BadKarma.supp.englab.local</arg>
</detail>
</message>
<message date="1306349726" file="" line="">
<detail id="3342866903">
<arg type="0">Operation timed out</arg>
<arg type="0">10.140.101.221</arg>
<arg type="3">16384</arg>
</detail>
</message>
</diagnostic>

I am able to pull all the message strings out with a query like this.

SELECT my.blob.value('.','varchar(MAX)') AS xmlstuff
FROM xmlblob
CROSS APPLY detailsxml.nodes('/diagnostic/message') as my(blob)

but I really only want specific ones and banging my head trying to figure out the right syntax after poking around several sites.

Anyone know this one off the top of your head?

-mark

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-12 : 16:56:43

Like this??


Declare @xmlblob table (
	detailsxml xml
)

Insert Into @xmlblob
Select detailsxml = '<summary />
<errors>
<error date="1306349726">
<detail id="3363983169" />
</error>
</errors>
<warnings />
<diagnostic>
<message date="1306349203" file="" line="">
<detail id="1195383211">
<arg type="0">The agent is about to bounce/reconnect.</arg>
</detail>
</message>
<message date="1306349233" file="" line="">
<detail id="1195383211">
<arg type="0">The agent is about to bounce/reconnect to server: BadKarma.supp.englab.local</arg>
</detail>
</message>
<message date="1306349338" file="" line="">
<detail id="1195383211">
<arg type="0">The agent is about to bounce/reconnect to server: BadKarma.supp.englab.local</arg>
</detail>
</message>
<message date="1306349442" file="" line="">
<detail id="1195383211">
<arg type="0">The agent is about to bounce/reconnect to server: BadKarma.supp.englab.local</arg>
</detail>
</message>
<message date="1306349547" file="" line="">
<detail id="1195383211">
<arg type="0">The agent is about to bounce/reconnect to server: BadKarma.supp.englab.local</arg>
</detail>
</message>
<message date="1306349651" file="" line="">
<detail id="1195383211">
<arg type="0">The agent is about to bounce/reconnect to server: BadKarma.supp.englab.local</arg>
</detail>
</message>
<message date="1306349726" file="" line="">
<detail id="3342866903">
<arg type="0">Operation timed out</arg>
<arg type="0">10.140.101.221</arg>
<arg type="3">16384</arg>
</detail>
</message>
</diagnostic>'


SELECT 
	my.blob.query('.') AS xmlstuff
FROM @xmlblob
CROSS APPLY detailsxml.nodes('/diagnostic/message[detail/arg[@type="0"]="Operation timed out"]') as my(blob)

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources