?
Solved

filter nodes with xquery

Posted on 2010-01-12
5
Medium Priority
?
730 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

777 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