Cannot implicitly atomize or apply 'fn:data()'

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)

Cannot implicitly atomize or apply 'fn:data()'

Author

Topic

bonekrusher
Starting Member

44 Posts

Posted - 2008-10-30 : 11:55:39

Hi,

I have a field that holds xml in a field type xml called xmlcontent. I am trying to use xquery to get all the partno's and its preceding-sibling cage. However I receive this error:

Error:

XQuery [tbl_sometable.xmlcontent.value()]: 
Cannot implicitly atomize or apply 'fn:data()' to complex content elements, found type 
'xs:anyType' within inferred type 'element(geeps{http://www.geeps/schema}:cage,xs:anyType) ?'.

XML stored in column:

<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>

SQL:

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 tbl_sometable
CROSS APPLY xmlcontent.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-30 : 12:13:04

may be this

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

bonekrusher
Starting Member

44 Posts

Posted - 2008-10-30 : 12:49:12

visakh16,

You're the best... Thanks.

Question. I am familiar with xpath, and a little xquery, where can I find a good SQL SERVER XMl book. I own "Professional SQL Server 2005 XML:" from wrox, but it just skims over xquery. Any good books to recommend?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 13:08:05

Cheers