Xquery - return rows - not single string

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 rows - not single string

Author

Topic

bonekrusher
Starting Member

44 Posts

Posted - 2008-10-28 : 13:19:40

Hi, Using the following t-sql, I want to return each xml node as a row. The below t-sql returns all the nodes in a single set.

SELECT xmlcontent.query('declare namespace geeps="http://www.geeps/schema"; 
(for $x in //geeps:part_number order by $x return data($x))' ) as Partno
FROM tbl_GEEPS

Currently it returns as:

part1234part1234

I want

part1234
part1234
...

Thanks for the help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 00:22:12

how is your xml structure?

bonekrusher
Starting Member

44 Posts

Posted - 2008-10-29 : 06:15:40

Hi,

My data looks like this in a column (xml):

<geeps:root xmlns:geeps="http://www.geeps/schema">
	<geeps:data1>
		<geeps:subdata>123234</geeps:subdata>
	</geeps:data1>
	<geeps:data2>
		<geeps:part_number>12234</geeps:part_number>
		<geeps:part_number>1ASD4</geeps:part_number>
		<geeps:part_number>12D6674</geeps:part_number>
		<geeps:part_number>1220998</geeps:part_number>
		<geeps:part_number>122323234</geeps:part_number>
		<geeps:part_number>12564565234</geeps:part_number>
		<geeps:part_number>1223464564</geeps:part_number>
	</geeps:data2>
</geeps:root>

I would like to "shred" the xml and return

Part No
-------
12234
1ASD4
12D6674
1220998
122323234
12564565234
1223464564

thanks for the help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 06:23:17

try this:-

declare @xml xml

set @xml='<geeps:root geeps="http://www.geeps/schema">
	<geeps:data1>
		<geeps:subdata>123234</geeps:subdata>
	</geeps:data1>
	<geeps:data2>
		<geeps:part_number>12234</geeps:part_number>
		<geeps:part_number>1ASD4</geeps:part_number>
		<geeps:part_number>12D6674</geeps:part_number>
		<geeps:part_number>1220998</geeps:part_number>
		<geeps:part_number>122323234</geeps:part_number>
		<geeps:part_number>12564565234</geeps:part_number>
		<geeps:part_number>1223464564</geeps:part_number>
	</geeps:data2>
</geeps:root>'

select @xml.query('data(declare namespace geeps="http://www.geeps/schema";/geeps:root/geeps:data2/geeps:part_number)')

bonekrusher
Starting Member

44 Posts

Posted - 2008-10-29 : 06:51:15

Hi, Thanks for the help.

I can get this to work:

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:part_number>12234</geeps:part_number>
		<geeps:part_number>1ASD4</geeps:part_number>
		<geeps:part_number>12D6674</geeps:part_number>
		<geeps:part_number>1220998</geeps:part_number>
		<geeps:part_number>122323234</geeps:part_number>
		<geeps:part_number>12564565234</geeps:part_number>
		<geeps:part_number>1223464564</geeps:part_number>
	</geeps:data2>
</geeps:root>'
SELECT T.c.value('declare namespace geeps="http://www.geeps/schema"; (.)[1]', 'varchar(100)') AS result
FROM   @x.nodes('declare namespace geeps="http://www.geeps/schema"; (/geeps:root/geeps:data2/geeps:part_number)') T(c)
GO

However, my xml is stored in a XML column.

When I try:

SELECT T.c.value('declare namespace geeps="http://www.geeps/schema"; (.)[1]', 'varchar(100)') AS result
FROM tbl_manuals.xmlcontent.nodes('declare namespace geeps="http://www.geeps/schema"; (/geeps:root/geeps:data2/geeps:part_number)') T(c)

I get the error:

Msg 107, Level 15, State 1, Line 30
The column prefix 'tbl_manuals' does not match with a table name or alias name used in the query.
Msg 9506, Level 16, State 1, Line 30
The XMLDT method 'nodes' can only be invoked on columns of type xml.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 06:57:23

wat about this?

SELECT T.c.value('declare namespace geeps="http://www.geeps/schema"; (.)[1]', 'varchar(100)') AS result
FROM tbl_manuals
CROSS APPLY xmlcontent.nodes('declare namespace geeps="http://www.geeps/schema"; (/geeps:root/geeps:data2/geeps:part_number)') T(c)

bonekrusher
Starting Member

44 Posts

Posted - 2008-10-29 : 07:00:18

Ok, I got this to work, but it doesn't seem efficient, since I am doing one query to load the xml into a variable, then querying the variable:

declare @x xml;

SELECT @x= xmlcontent.query( N'
declare namespace geeps="http://www.geeps/schema"; /geeps:root
 ') from tbl_geia

SELECT T.c.value('declare namespace geeps="http://www.geeps/schema; (.)[1]', 'varchar(100)') AS Part_Number
FROM @x.nodes('declare namespace geeps="http://www.geeps/schema"; (/geeps:root/geeps:data2/geeps:part_number)') T(c)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 07:00:53

and this also

SELECT xmlcontent.query('value(declare namespace geeps="http://www.geeps/schema"; (/geeps:root/geeps:data2/geeps:part_number))') AS result
FROM tbl_manuals

bonekrusher
Starting Member

44 Posts

Posted - 2008-10-29 : 07:12:36

Hi, The following returns an empty set...

SELECT T.c.value('declare namespace geeps="http://www.geeps/schema"; (.)[1]', 'varchar(100)') AS result
FROM tbl_manuals
CROSS APPLY xmlcontent.nodes('declare namespace geeps="http://www.geeps/schema"; (/geeps:root/geeps:data2/geeps:part_number)') T(c)

Not sure why... The xpath is the same a the working example.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 07:49:21

and what about second suggestion using query()?

bonekrusher
Starting Member

44 Posts

Posted - 2008-10-29 : 08:06:23

Running:

SELECT xmlcontent.query('value(declare namespace geeps="http://www.geeps/schema"; (/geeps:root/geeps:data2/geeps:part_number))') AS result
FROM tbl_manuals
GO

I get error:

Msg 2217, Level 16, State 1, Line 13
XQuery [tbl_manuals.xmlcontent.query()]: ',' or ')' expected

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 08:10:17

wat about this?

SELECT xmlcontent.query('declare namespace geeps="http://www.geeps/schema"; value(/geeps:root/geeps:data2/geeps:part_number)') AS result
FROM tbl_manuals
GO

bonekrusher
Starting Member

44 Posts

Posted - 2008-10-29 : 08:14:30

Ok,

this works (your example)!

SELECT T.c.value('declare namespace geeps="http://www.geeps/schema"; (.)[1]', 'varchar(100)') AS result
FROM tbl_manuals
CROSS APPLY xmlcontent.nodes('declare namespace geeps="http://www.geeps/schema"; (/geeps:root/geeps:data2/geeps:part_number)') T(c)

I was querying the wrong table.

Thanks you very very much!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 08:16:06

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources