SQL Server 2005 XML exists query question 2

[blondebier]

Hi Guys,

I'm trying to pull together a query on an XML document in SQL Server 2005.

I have some XML like this :

<test_xml>
<element1 attribute="A" />
<element2 attribute="B" />
<element1 attribute="C" />
<element2 attribute="D" />
</test_xml>

Basically I want to find out whether there exists an element called "element2" and it's matching attribute is either "A", "B", "C" or "D"

I thought this would be pretty straight forward using the exists keyword.

i.e. @foundit = test_xml.exists('/test_xml[1]/element2[contains(@attribute, "A")]')

Can I specify all the required attribute values using "contains"?

Any tips would be most helpful.

Cheers,

Blondebier

 

[hmckillop]

This might help , dont have time to look into this more

create table #xml_temp (
xml_col xml
)
insert into #xml_temp values('<test_xml>
    <element1 attribute="A" />
    <element2 attribute="B" />
    <element1 attribute="C" />
    <element2 attribute="D" />
</test_xml>')

select xml_col.query('data(/test_xml/element1/@attribute)') from #xml_temp
select xml_col.exist('data(/test_xml/element2)') , xml_col.query('data(/test_xml/element2/@attribute)') 
from #xml_temp

"I'm living so far beyond my income that we may almost be said to be living apart

 

[blondebier]

Thanks, but it didn't really help.

 

[hmckillop]

I think it contained the base info for helping. e.g.

declare @v_DoesExist int
select @v_DoesExist = 
case when xml_col.exist('data(/test_xml/element2)') > 0 and charindex(convert(varchar(10),xml_col.query('data(/test_xml/element2/@attribute)')), 'A') > 0 then 1 else 0 end  
from #xml_temp

select @v_DoesExist

"I'm living so far beyond my income that we may almost be said to be living apart

 

[tsuji]

A:>Thanks, but it didn't really help.
B:>I think it contained the base info for helping.
The latter is more truthful. If one has not much of an idea of xml data type query, then anything might not help.

I would suggest an alternative to hmckillop's suggestion by making good use of xpath. At the risk of un-illustrative repetition, I would restrict to "A" and "B" only.
[tt]
declare @v_DoesExist int
select @v_DoesExist = xml_col.exist('data(/test_xml/element2[contains(@attribute,"A") or contains(@attribute,"B")]') from #xml_temp
select @v_DoesExist
[/tt]
If the test is for exact match, use simply equal.
[tt]
declare @v_DoesExist int
select @v_DoesExist = xml_col.exist('data(/test_xml/element2[@attribute="A" or @attribute="B"]') from #xml_temp
select @v_DoesExist
[/tt]

 

[hmckillop]

Good post tsuji

"I'm living so far beyond my income that we may almost be said to be living apart

 

[blondebier]

Thanks tsuji, that's exactly what I was after.