MS SQL 2005 and @XML.exist

I'm trying to store a list of data in XML variable and after I need to make a test to see if a string exists.


In my example the string I'm looking for is "Test4", but not exists in the XML.

Why when I used the @xml.exist('sql:variable("@xPath")')  the results is wrong?

declare @xml xml
set @xml =
'<?xml version="1.0" encoding="utf-8"?>
<attributes>
	<attribute  txt="test1" notes="blablaablalb"/>
	<attribute  txt="test2" notes="blablaablalb"/>
	<attribute  txt="test3" notes="blablaablalb"/>
</attributes>'

select 
	@xml.exist('/attributes/attribute[@txt = "test4"]') 

-- return 0

declare @xPath varchar(10)

set @xPath = '/attributes/attribute[@txt = "test4"]'

select 
	@xml.exist('sql:variable("@xPath")') 

-- return 1

Open in new window


Can you please help to understand what I'm doing wrong or how can I used @xml.exist whit dynamics SQL?


 
LVL 7
CboudrozAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
And this may be what you are really after and why varchar was originally only set to 10.
declare @xml xml
set @xml =
'<?xml version="1.0" encoding="utf-8"?>
<attributes>
	<attribute  txt="test1" notes="blablaablalb"/>
	<attribute  txt="test2" notes="blablaablalb"/>
	<attribute  txt="test3" notes="blablaablalb"/>
</attributes>';
declare @txt varchar(10)
set @txt = 'test4';
select @txt
     , @xml.exist('/attributes/attribute[@txt = sql:variable("@txt")]')

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Cboudroz,

This doesn't fix the ultimate issue, but your varchar(10) isn't big enough for the XPath, so fix that to varchar(50) or something of that size.  Then you may be able to use dynamic SQL like this.
declare @xPath nvarchar(50), @sql nvarchar(200)

set @xPath = N'/attributes/attribute[@txt = "test4"]'
set @sql = N'select @xmlFrag.exist('''+ @xPath +''')'

exec sp_executeSQL @sql, N'@xmlFrag xml', @xmlFrag = @xml; 

Open in new window

0
 
CboudrozAuthor Commented:
mwvisa1:

sorry for varchar(10) that wasn't the original code.  The problems was that I didn't correctly used the sql:variable function.  

Thanks for your help.



0
All Courses

From novice to tech pro — start learning today.