test if xml node exists - SQL Server Forums

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)

test if xml node exists

Author

Topic

olibara
Yak Posting Veteran

94 Posts

Posted - 2013-04-08 : 04:25:27

Hello

I have get ifo from XML fiels in a Database (horror IMO)

So to get the name of a seller I use the following select :

[CODE]Select
Basket.ExtraInfo.value('(//Plugin[@name=''ClientAVendorInfoPlugin'']//Property[@key=''Name''])[1]','nvarchar(200)') AS 'Seller'
From Ventes[/CODE]

Unfortunately in some case the node have a different name

[CODE]Select
Basket.ExtraInfo.value('(//Plugin[@name=''ClientABCVendorInfoPlugin'']//Property[@key=''Name''])[1]','nvarchar(200)') AS 'Seller'
From Ventes[/CODE]

I would like to make a CASE to test the existence of one or other node
But I'm not an XML select expert to get the syntax for that

Thanks for your help

Merci de votre aide

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-08 : 09:12:04

Wouldn't using OR do the same thing?

Select
Basket.ExtraInfo.value('(//Plugin[@name=''ClientAVendorInfoPlugin'' or 
	[@name=''ClientABCVendorInfoPlugin'']//Property[@key=''Name''])[1]','nvarchar(200)') AS 'Seller'
From Ventes

rama108
Posting Yak Master

115 Posts

Posted - 2013-10-22 : 10:38:15

I need to know if the xml node record exists in table.

SELECT col1,Col2 from tblA
WHERE @xml.exist('(/List/Element[@ID = col1 and @ID2 = col2

how do i construct this?

Thank you.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-22 : 11:59:49

Like this:

WHERE @xml.exist('/List/Element[@ID = sql:variable("col1")][@ID2 = sql:variable("col2")]') = 1

When you want to ask a question, it is better to start a new thread than append to an existing old thread.

rama108
Posting Yak Master

115 Posts

Posted - 2013-10-22 : 12:33:54

Thank you James for your time, that works perfectly.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-22 : 13:04:34

You are very welcome - glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources