Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

filter nodes with xquery

Posted on 2010-01-12
5
Medium Priority
?
735 Views
Last Modified: 2013-11-05
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>
0
Comment
Question by:DaFou
  • 2
  • 2
5 Comments
 
LVL 11

Expert Comment

by:rajvja
ID: 26291832
0
 
LVL 2

Author Comment

by:DaFou
ID: 26291987
that link is useless
0
 
LVL 5

Expert Comment

by:strider_1981
ID: 26292249
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

0
 
LVL 2

Author Comment

by:DaFou
ID: 26292381
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.
0
 
LVL 5

Accepted Solution

by:
strider_1981 earned 2000 total points
ID: 26294070
I dont think u can perform 'Select' and 'Modify' at the same time. It's like executing Update query in a select statement. However here's a work arround tht i have got for u. see if fits the bill. I created a dummy test table to give u a clear idea. All the scripts are included in the code below.
Cheers,
Amit
/***************************Test Table Creation************************/
CREATE TABLE [dbo].[TestTable](
	[RowID] [int] IDENTITY(1,1) NOT NULL,
	[RowName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Data] [xml] NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
	[RowID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
/*********************************************************************/
/***************************Insert Values*****************************/
Declare @Request 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"/>
		<Culture cultureName="en-EN" entity="Dame" description="Tranga"/>
		<Culture cultureName="en-SN" entity="Dame" description="Tranga"/>
	</Cultures>
	<SomeOtherSutff>
		hohohoho
	</SomeOtherSutff>
</Properties>
'
Insert	Into TestTable
		(
			RowName
			,Data
		)
Values	(
			'Row 1 Data'
			,@Request
		)

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"/>
		<Culture cultureName="en-EN" entity="Dame" description="Tranga"/>
	</Cultures>
	<SomeOtherSutff>
		hohohoho
	</SomeOtherSutff>
</Properties>
'
Insert	Into TestTable
		(
			RowName
			,Data
		)
Values	(
			'Row 2 Data'
			,@Request
		)

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>
'
Insert	Into TestTable
		(
			RowName
			,Data
		)
Values	(
			'Row 3 Data'
			,@Request
		)
/*********************************************************************/
/***************************Select Logic *****************************/

/*Dont perform 'modify' in the select statement. Instead get the whole xml in a variable and the do the 'modify' thingy*/
Set @Request = (
			Select	RowID
					,RowName
					,Data
			From	TestTable
			For		XML Auto,Root('RootData')
		)

Set	@Request.modify('delete (//Properties/Cultures/Culture[@cultureName != "en-GB"])')

/*Get the data in XML format*/
Select	@Request

/*Get the data in tabular format*/
Select	A.B.value('@RowID', 'Int') As RowID
		,A.B.value('@RowName', 'Varchar(50)')
		,A.B.query('*')
From	@Request.nodes('//RootData/TestTable') A(B)
/*********************************************************************/

Open in new window

0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Loops Section Overview
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question