Working with Multiple XML Docs

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)

Working with Multiple XML Docs

Author

Topic

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2012-10-30 : 13:44:53

We have a vended app and in one of the tables is a field that is xml varbinary. If I cast it I can read it as XML. Here is what I have done so far:

declare @MyXML XML
declare @MyXMLTrim XML

SET @MyXML =
(
SELECT CAST(PF.Prof as xml)
FROM table1 AS PF INNER JOIN table2 P
ON P.pid = PF.pid
WHERE P.extusername = '123456789'
)

SET @MyXMLTrim = REPLACE(CAST(@MyXML as VARCHAR(MAX)),' xmlns="http://schemas.clairmail.com/2008/01/Model/extensions"','')

SELECT
a.b.value('properties[1]/property[1]/value[1]','int') AS RiskCode
FROM @MyXMLTrim.nodes('profile') a(b)

That will return me the value of 11, which is what I want.
But each Entity in table1 has it's own xml field so that where clause will be removed. Not sure how to do a loop with this.

What I would like to do is a count of all records where that value is an 11, 22, 33, or 44.

Thoughts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 13:57:24

[code]
SELECT COUNT(*)
FROM
(
SELECT a.b.value('properties[1]/property[1]/value[1]','int') AS RiskCode
FROM
(
SELECT CAST( REPLACE(CAST(PF.Prof as VARCHAR(MAX)),' xmlns="http://schemas.clairmail.com/2008/01/Model/extensions"','')as xml) AS MyXML
FROM table1 AS PF INNER JOIN table2 P
ON P.pid = PF.pid
)m
CROSS APPLY MyXML.nodes('profile')a(b)
)r
WHERE RiskCode IN (11,22,33,44)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2012-10-30 : 14:14:57

visakh16 you are my hero.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 14:36:42

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources