Querying XML stored as NVARCHAR

Question

Post reply

Querying XML stored as NVARCHAR

EnigmaticSoul

SSC-Addicted

Points: 405
More actions
February 25, 2010 at 11:45 am

#103904

Hello,
After looking over many many other posts, both here and on MSDN, I am still struggling with how to query XML data from my table. I'm hoping that someone here might have a moment to take a look and perhaps offer some advice.
I have a table that looks like this:
CREATE TABLE [dbo].[WfSharedOperations](
[guid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[data] [nvarchar](max) NOT NULL,
[version] [uniqueidentifier] NOT NULL,
[modified] [datetime] NOT NULL,
CONSTRAINT [PK_WfSharedOperations] PRIMARY KEY CLUSTERED
In the "data" column, I have XML data (but changing the data type to XML is not an option, unfortunately.
The XML content looks like something like this:
<Operation xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:schemas-quest-com:ActiveRolesServer" type="Deprovision" cleanupTaskID="1c20cf86-6149-4bca-ae53-e7d897e9c4ea" databaseGuid="26baa8f8-618e-49b5-ac49-a32d21664a7e" serverGuid="a68e42e3-98f3-4ca6-9dff-c89bfb496f60" id="1-692" status="Completed" creationTime="2010-02-09T16:36:44.5564683Z" modificationTime="2010-02-09T16:36:46.8847572Z" readyForCleanup="false" guid="e26b2d17-4835-49dc-aed8-4c934517346a">
<Initiator dn="CN=Administrator,CN=Users,DC=demo,DC=com" objectClass="user" guid="c2cb4cda-e34a-4bec-b738-212cef61acfe" sid="S-1-5-21-1821952266-3021913658-707382108-500" ntAccount="DEMO\administrator" computer="rsrv1.demo.com" site="Default-First-Site-Name" isadmin="true">
</Initiator>
</Operation>
Finally, my SQL query looks like this:
DECLARE @v0 UNIQUEIDENTIFIER, @v2 UNIQUEIDENTIFIER, @Action NVARCHAR (11)
SET @v0 = 'F5634809-6311-4250-846A-71ADB82A8C4C'
SET @v2 = '792B8803-A91B-4E58-809A-DE07094F4715'
SET @Action = 'Deprovision'
SELECT o.[guid],
o.[modified],
CAST (o.[data] AS XML).value ('(/Operation[1]/Initiator[1]/@dn[1])[1]', 'nvarchar(255)') AS xmlChangeHistory
FROM (SELECT m0.[operation]
FROM (SELECT DISTINCT [operation]
FROM [WfOperationValues]
WHERE [property] = @v0) AS m0
INNER JOIN
(SELECT DISTINCT [operation]
FROM [WfOperationValues]
WHERE [property] = @v2 AND [value_short] = @Action) AS m1
ON m0.[operation] = m1.[operation]) AS k
INNER JOIN
[WfOperations] AS o
ON k.[operation] = o.[guid]
ORDER BY o.[modified] DESC, o.[guid] ASC
The o.data.value function comes back with null every time. I've experimented with using CROSS APPLY, but that didn't get me anywhere either. This post seemed to be very close to what I was looking for, but I just don't have the smarts to put two and two together here, it seems. Any advice is most appreciated.
FYI, yes, I've also tried "value ('(/Operation/Initiator/@dn)[1]'", but that doesn't work any better.
Cheers and thanks for reading,
EnigmaticSoul

Viewing 8 posts - 1 through 8 (of 8 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Try:

CAST (o.[data] AS XML).value ('(/Operation/Initiator/@dn)[1]', 'nvarchar(255)') AS xmlChangeHistory

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

EnigmaticSoul SSC-Addicted Points: 405 More actions · Answer 2

Matt Miller (#4) (2/25/2010)
Try:
CAST (o.[data] AS XML).value ('(/Operation/Initiator/@dn)[1]', 'nvarchar(255)') AS xmlChangeHistory

Hello, Matt.

Unfortunately I did already try that. See:

EnigmaticSoul (2/25/2010)
FYI, yes, I've also tried "value ('(/Operation/Initiator/@dn)[1]'", but that doesn't work any better.

Thanks for taking the time to reply anyway.

Cheers,

EnigmaticSoul

LutzM SSC Guru Points: 107049 More actions · Answer 3

Since you're using a typed xml document you need to use XML NAMESPACES to qualify your elements (even though the namespace is nowhere used inside the xml document).

Since I couldn't convert the sample snippets you provided into runable code I designed my own test data:

DECLARE @tbl TABLE( id int, data varchar(max))

declare @xml varchar(max)

set @xml='

<Initiator dn="CN=Administrator,CN=Users,DC=demo,DC=com" objectClass="user" guid="c2cb4cda-e34a-4bec-b738-212cef61acfe" sid="S-1-5-21-1821952266-3021913658-707382108-500" ntAccount="DEMO\administrator" computer="rsrv1.demo.com" site="Default-First-Site-Name" isadmin="true">

</Initiator>

</Operation>'

insert into @tbl

select 1,@xml

; WITH XMLNAMESPACES (

DEFAULT 'urn:schemas-quest-com:ActiveRolesServer'

)

SELECT id,

convert ( XML,t.[data]).value ('(/Operation[1]/Initiator[1]/@dn[1])[1]', 'nvarchar(255)') AS xmlChangeHistory

FROM @tbl t

/* result set

idxmlChangeHistory

1CN=Administrator,CN=Users,DC=demo,DC=com

*/

I hope you can use that snippet to change your query.

If you get stuck please provide ready to use test data together with your expected result.

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]

EnigmaticSoul SSC-Addicted Points: 405 More actions · Answer 4

Thank you, SSCrazy, for the reply - it sent me in the right direction. I think I found a much easier way to tackle the problem, though:

CAST (o.[data] AS XML).value ('declare default element namespace "urn:schemas-quest-com:ActiveRolesServer"; (/Operation/Initiator/@dn)[1]', 'nvarchar(255)') AS xmlChangeHistory

It now works! Can't tell you how long that's been plaguing me. Thanks again for the advice!

Cheers,

EnigmaticSoul

LutzM SSC Guru Points: 107049 More actions · Answer 5

Glad you found a solution that works for you and thank you for posting back.

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]

Paul White SSC Guru Points: 150468 More actions · Answer 6

EnigmaticSoul (2/25/2010)
TI think I found a much easier way to tackle the problem, though:
CAST (o.[data] AS XML).value ('declare default element namespace "urn:schemas-quest-com:ActiveRolesServer"; (/Operation/Initiator/@dn)[1]', 'nvarchar(255)') AS xmlChangeHistory

Not as easy as using the WITH_XMLNAMESPACES clause though!

Click that link to see how to specify a default XML namespace from T-SQL. It's easier.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

LutzM SSC Guru Points: 107049 More actions · Answer 7

Paul White (2/27/2010)
Not as easy as using the WITH_XMLNAMESPACES clause though!
Click that link to see how to specify a default XML namespace from T-SQL. It's easier.
Paul

... or look a few posts back to see a solution applied to your scenario

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]

Querying XML stored as NVARCHAR

Cookies on SQLServerCentral