xquery - return context node and sibling

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

xquery - return context node and sibling

Author

Topic

bonekrusher
Starting Member

44 Posts

Posted - 2008-10-30 : 09:24:37

Hi, given the following example, how would I return the preceding-sibling geeps:cage along with result set:

DECLARE @x xml 
SET @x='<geeps:root xmlns:geeps="http://www.geeps/schema">
	<geeps:data1>
		<geeps:subdata>123234</geeps:subdata>
	</geeps:data1>
	<geeps:data2>
		<geeps:cage>09998</geeps:cage>
		<geeps:part_number>12234</geeps:part_number>
	</geeps:data2>
	<geeps:data2>
		<geeps:cage>04568</geeps:cage>
		<geeps:part_number>1ASD4</geeps:part_number>
	</geeps:data2>
	<geeps:data2>
		<geeps:cage>ASBB7</geeps:cage>
		<geeps:part_number>ZZZSSD4</geeps:part_number>
	</geeps:data2>
</geeps:root>'
SELECT T.c.value('declare namespace geeps="http://www.geeps/schema"; (.)[1]', 'varchar(100)') AS partno
FROM @x.nodes('declare namespace geeps="http://www.geeps/schema"; (/geeps:root/geeps:data2/geeps:part_number)') T(c)

GO

partno | cage
--------------
12234 | 09998
1ASD4 | 04568
ZZZSSD4 | ASBB7

Thanks for the help

Desire output:

bonekrusher
Starting Member

44 Posts

Posted - 2008-10-30 : 09:47:20

Got it

DECLARE @x xml 
SET @x='<geeps:root xmlns:geeps="http://www.geeps/schema">
	<geeps:data1>
		<geeps:subdata>123234</geeps:subdata>
	</geeps:data1>
	<geeps:data2>
		<geeps:cage>09998</geeps:cage>
		<geeps:part_number>12234</geeps:part_number>
	</geeps:data2>
	<geeps:data2>
		<geeps:cage>04568</geeps:cage>
		<geeps:part_number>1ASD4</geeps:part_number>
	</geeps:data2>
	<geeps:data2>
		<geeps:cage>ASBB7</geeps:cage>
		<geeps:part_number>ZZZSSD4</geeps:part_number>
	</geeps:data2>
</geeps:root>'
SELECT T.c.value('declare namespace geeps="http://www.geeps/schema"; (.)[1]', 'varchar(100)') AS partno,
T.c.value('declare namespace geeps="http://www.geeps/schema"; (../geeps:cage)[1]', 'varchar(100)') AS cageno
FROM @x.nodes('declare namespace geeps="http://www.geeps/schema"; (/geeps:root/geeps:data2/geeps:part_number)') T(c)

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources