FOR XML PATH and querying an XML Data Type 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 2005 Forums

Transact-SQL (2005)

FOR XML PATH and querying an XML Data Type column

Author

Topic

hshot_rooke
Starting Member

8 Posts

Posted - 2007-10-24 : 13:13:55

Hey everyone

I've been using FOR XML EXPLICIT for about 2 years now and my company is starting to switch over to FOR XML PATH queries.

Here is my query:


	SELECT
		SeID					"@sessionid",
		WebID					"@webid",
		Cast(Data as nvarchar(max))					"@data"
	FROM Account_Sessions
	WHERE	(SeID = '5E9E878B-A94A-4466-954C-37C2E4FEB649') AND
			(CliID = 1)
	FOR XML PATH('accountsession'),ROOT('ROOT')

Sorry, seems like when posting the resultset, it looks fine when viewing the post as it's just markup with < and >.

I've tried casting the datatype to an nvarchar(max) and this works, but I'm guessing this isn't possible since it's an XML document inside of another XML from the outer query.

Anyone have any suggestions on how I can bring my "SeID" and "WebID" out along with the "Data" column in one resultset XML?

Any help appreciated. Thanks in advance.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-24 : 13:23:19

so your desired result would be?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

hshot_rooke
Starting Member

8 Posts

Posted - 2007-10-24 : 13:28:52

Sorry, guess I wasn't specific to what I would like in results.

I think I've found a solution, as I can bring back the encoded markup and decode it on the client side using .NET.

I'm just wondering if anyone else has run into this problem and what their solution is. Seems like a backwards way since I have to:

1) Bring the XML back to the client side
2) Load the XML into a DOM, decode the Data XML string
3) Load the decoded XML into another DOM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources