How to get specific data from XML?

Question

Post reply

How to get specific data from XML?

mamidimahesh-127681

SSC Eights!

Points: 868
More actions
January 20, 2009 at 4:17 am

#125777

Hi All,
I have XML file (attached with this topic). In this I have few rule names like CEMPID, CONTACTID, GROUPOPTYID etc..., If I want to get simpletext node value of ContactID rule. How do I write my query to pull this specific data from an XML?
Thanks in advance
Mahesh Mamidi

Viewing 6 posts - 1 through 5 (of 5 total)

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

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

This will do

Note that I have replaced XML tags with []

DECLARE @x XML

SELECT @x = '

[SQLRuleSet

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"]

[SQLRule rulename="CEMPID" ruletype="SQLText" comment="" DB="SIEBEL" enable="1"]

[simpletext]select top(100) ROW_ID from S_CONTACT where EMP_FLG = ''Y'' [/simpletext]

[/SQLRule]

[SQLRule rulename="ContactID" ruletype="SQLText" comment="" DB="SIEBEL" enable="1"]

[simpletext]select top 100 row_id as ''Contact ID'' from s_contact [/simpletext]

[/SQLRule]

[SQLRule rulename="GroupOptyID" ruletype="SQLText" comment="" DB="SIEBEL" enable="1"]

[simpletext]Select top(30)ROW_ID from S_OPTY where X_OPTY_TYPE =

''Group''and BU_ID = ''1-8471-83'' and STATUS_CD = ''active''

and created between ''2004-2-5'' and ''2005-9-10'' [/simpletext]

[/SQLRule]

[/SQLRuleSet]'

SELECT

x.value('.','VARCHAR(50)')

FROM @x.nodes('SQLRuleSet/SQLRule[@rulename="ContactID"]/simpletext') a(x)

/*

--------------------------------------------------

select top 100 row_id as 'Contact ID' from s_conta

*/

.

mamidimahesh-127681 SSC Eights! Points: 868 More actions · Answer 2

Thanks a lot. This query helps me.

Regards,

Mahesh Mamidi

mamidimahesh-127681 SSC Eights! Points: 868 More actions · Answer 3

I changed above query as shown below. It shows "The argument 1 of the XML data type method "nodes" must be a string literal." error. Can't I use string variable?

DECLARE @x XML, @table varchar(50), @nodestr varchar(1000)

SET @table = 'ContactID'

SET @nodestr = 'SQLRuleSet/SQLRule[@rulename="' + @table + '"]/simpletext'

SELECT @x = '

[SQLRuleSet

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"]

[SQLRule rulename="CEMPID" ruletype="SQLText" comment="" DB="SIEBEL" enable="1"]

[simpletext]select top(100) ROW_ID from S_CONTACT where EMP_FLG = ''Y'' [/simpletext]

[/SQLRule]

[SQLRule rulename="ContactID" ruletype="SQLText" comment="" DB="SIEBEL" enable="1"]

[simpletext]select top 100 row_id as ''Contact ID'' from s_contact [/simpletext]

[/SQLRule]

[SQLRule rulename="GroupOptyID" ruletype="SQLText" comment="" DB="SIEBEL" enable="1"]

[simpletext]Select top(30)ROW_ID from S_OPTY where X_OPTY_TYPE =

''Group''and BU_ID = ''1-8471-83'' and STATUS_CD = ''active''

and created between ''2004-2-5'' and ''2005-9-10'' [/simpletext]

[/SQLRule]

[/SQLRuleSet]'

SELECT

x.value('.','VARCHAR(50)')

FROM @x.nodes(@nodestr) a(x)

Regards,

Mahesh Mamidi

jacob sebastian SSChampion Points: 11822 More actions · Answer 4

You cannot use replace the node with a variable. Instead, you can achieve the same results using the following query:

the following example shows how to use variables in an XPATH expression.

DECLARE @table VARCHAR(20)

SET @table = 'ContactID'

SELECT

x.value('.','VARCHAR(50)')

FROM @x.nodes('SQLRuleSet/SQLRule[@rulename=sql:variable("@table")]/simpletext') a(x)

/*

--------------------------------------------------

select top 100 row_id as 'Contact ID' from s_conta

*/

I would recommend reading the XQuery tutorials at http://blog.sqlserver.me/2008/06/xquery-labs-collection-of-xquery-sample.html. I have included several examples that show how to work with variables in XQuery/XPath.

.

hbpank SSC Eights! Points: 828 More actions · Answer 5

Hi Jacob,

Thanks a lot, you have compiled very good information on XML in the Blog link.

Hary Pank

Thanks a lot,
Hary