asked on

filter nodes with xquery

HI All,

I have an xml field with the following contents:
<Properties>
<Cultures>
<Culture cultureName="en-GB" entity="Person" description="That dude that does stuff" />
<Culture cultureName="en-US" entity="Dude" description="Tranga" />
</Cultures>
<SomeOtherSutff>
hohohoho
</SomeOtherSutff>
</Properties>

How do I query that field to exclude nodes with a cultureName attribute other than 'en-GB'. Nodex without a cultureName attribute should stay included so the results should be this:
<Properties>
<Cultures>
<Culture cultureName="en-GB" entity="Person" description="That dude that does stuff" />
</Cultures>
<SomeOtherSutff>
hohohoho
</SomeOtherSutff>
</Properties>

rajvja

Visit this link

http://msdn.microsoft.com/en-us/library/ms187561.aspx

DaFou

ASKER

that link is useless

strider_1981

Hey DaFou,
Check the attached sql code snippet. Hope it works for u...
Cheers,
Amit

Declare @Request As XML
Declare @FilteredXML As XML
Set @Request = '
<Properties>
	<Cultures>
		<Culture cultureName="en-GB" entity="Person" description="That dude that does stuff"/>
		<Culture cultureName="en-GB" entity="Babe" description="This base does stuff"/>
		<Culture cultureName="en-US" entity="Dude" description="Tranga"/>
	</Cultures>
	<SomeOtherSutff>
		hohohoho
	</SomeOtherSutff>
</Properties>
'

/*
This statement will filter out nodes with value 'en-GB'. thus giving u output like
<Culture cultureName="en-GB" entity="Person" description="That dude that does stuff" />
<Culture cultureName="en-GB" entity="Babe" description="This base does stuff" />
*/
Set @FilteredXML = @Request.query('//Properties/Cultures/Culture[@cultureName = "en-GB"]')
Select	@FilteredXML 

/*
This statement will remove all the nodes which do not have value 'en-GB'. thus giving u output like
<Properties>
  <Cultures>
    <Culture cultureName="en-GB" entity="Person" description="That dude that does stuff" />
    <Culture cultureName="en-GB" entity="Babe" description="This base does stuff" />
  </Cultures>
  <SomeOtherSutff>
		hohohoho
	</SomeOtherSutff>
</Properties>

And i guess this is wht u r looking for....
*/
Set	@Request.modify('delete (//Properties/Cultures/Culture[@cultureName != "en-GB"])')
Select	@Request

Open in new window

DaFou

ASKER

I am running into some problems when putting this in a select statment

SELECT
1 AS Tag
, NULL AS Parent
, CAST(CAST(ep.value AS varchar(max)) AS xml).modify('delete (//Properties/Cultures/Culture[@cultureName != "en-GB"])')

etc
etc
....

error:
Incorrect use of the XML data type method 'modify'. A non-mutator method is expected in this context.

ASKER CERTIFIED SOLUTION

strider_1981

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial