OPENXML fails with namespace as root

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)

OPENXML fails with namespace as root

Author

Topic

VonC
Starting Member

6 Posts

Posted - 2009-04-07 : 09:27:08

Hi, trying to get some basic XML into SQL - this works (try it):

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc output,
'<?xml version="1.0" encoding="UTF-8"?>
<Projs>
<Proj>
<WCode>ZZ111</WCode>
<WDescription>Analysis</WDescription>
<WStatus>0</WStatus>
</Proj>
<Proj>
<WCode>ZZ222</WCode>
<WDescription>Design</WDescription>
<WStatus>1</WStatus>
</Proj>
<Proj>
<WCode>ZZAAA</WCode>
<WDescription>Program</WDescription>
<WStatus>0</WStatus>
</Proj>
</Projs>'
SELECT * FROM OPENXML(@hdoc, '/Projs/Proj', 2)
WITH (WCode varchar(80))
EXEC sp_xml_removedocument @hDoc

But this doesn't:

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc output,
'<?xml version="1.0" encoding="UTF-8"?>
<ns1:GSys_ProjRefDetail_MX xmlns:ns1="urn:com.CLIENT.GSys.ProjRef">
<Proj>
<WCode>ZZ111</WCode>
<WDescription>Analysis</WDescription>
<WStatus>0</WStatus>
</Proj>
<Proj>
<WCode>ZZ222</WCode>
<WDescription>Design</WDescription>
<WStatus>1</WStatus>
</Proj>
<Proj>
<WCode>ZZAAA</WCode>
<WDescription>Program</WDescription>
<WStatus>0</WStatus>
</Proj>
</ns1:GSys_ProjRefDetail_MX>'
SELECT * FROM OPENXML(@hdoc, '/ns1:GSys_ProjRefDetail_MX/Proj', 2)
WITH (WCode varchar(80))
EXEC sp_xml_removedocument @hDoc

... because of undeclared namespace. The first one works because the root node is fine, whereas the second one fails even though XMLSpy reckons '/ns1:GSys_ProjRefDetail_MX is fine for a root node (but still doesn't validate without a schema). Surely I don't need a schema for this, its almost there!

Hopefully someone has circumnavigated this, cheers.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 09:28:45

In SQL Server 2005, sp_xml_preparedocument is deprecated.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 09:33:46

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113872

DECLARE	@x XML

SET	@x = '<?xml version="1.0" encoding="UTF-8"?>
<ns1:GSys_ProjRefDetail_MX xmlns:ns1="urn:com.CLIENT.GSys.ProjRef">
<Proj>
<WCode>ZZ111</WCode>
<WDescription>Analysis</WDescription>
<WStatus>0</WStatus>
</Proj>
<Proj>
<WCode>ZZ222</WCode>
<WDescription>Design</WDescription>
<WStatus>1</WStatus>
</Proj>
<Proj>
<WCode>ZZAAA</WCode>
<WDescription>Program</WDescription>
<WStatus>0</WStatus>
</Proj>
</ns1:GSys_ProjRefDetail_MX>'

;WITH XMLNAMESPACES('urn:com.CLIENT.GSys.ProjRef' AS ns1)
SELECT	i.value('WCode[1]', 'VARCHAR(100)') AS WCode
FROM	@x.nodes('/ns1:Proj') AS y(i)

E 12°55'05.63"
N 56°04'39.26"

VonC
Starting Member

6 Posts

Posted - 2009-04-07 : 11:25:27

Many thanks Peso. This doesn't return any results though, and the other link is slightly different - also shows just how badly SSMS intellisense seems to support the queries (marking the references as ambiguous).

sp_xml_preparedocument might be becoming deprecated (in 2008 +) but its very much part of SQL2005, can't find any ref on MS to it's deprecation - you know more on this though, where does it say?

Appreciate the warning though, better to move to something that's maintained. Trouble is, documentation and examples for ;WITH XMLNAMESPACES is much thinner, so I can't immediately get your code to work without a lot of guessing, maybe you can cast some light on it?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-07 : 18:58:43

see last line....
DECLARE @x XML

SET @x = '<?xml version="1.0" encoding="UTF-8"?>
<ns1:GSys_ProjRefDetail_MX xmlns:ns1="urn:com.CLIENT.GSys.ProjRef">
<Proj>
<WCode>ZZ111</WCode>
<WDescription>Analysis</WDescription>
<WStatus>0</WStatus>
</Proj>
<Proj>
<WCode>ZZ222</WCode>
<WDescription>Design</WDescription>
<WStatus>1</WStatus>
</Proj>
<Proj>
<WCode>ZZAAA</WCode>
<WDescription>Program</WDescription>
<WStatus>0</WStatus>
</Proj>
</ns1:GSys_ProjRefDetail_MX>'

;WITH XMLNAMESPACES('urn:com.CLIENT.GSys.ProjRef' AS ns1)
SELECT i.value('WCode[1]', 'VARCHAR(100)') AS WCode
FROM @x.nodes('/ns1:GSys_ProjRefDetail_MX/Proj') AS y(i)

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

VonC
Starting Member

6 Posts

Posted - 2009-04-08 : 03:59:40

Fantastic, works a treat! Thanks for stepping in Yosiasz, well resolved, and to Peso for the original post(ings).

Still interested in the sp_xml_preparedocument deprecation confirmation, although I see it does not appear to handle namespaces. XMLNAMESPACES works well though (can't find conf of ANSI standard and MS need to use ';' in front of WITH to avoid parser problem with optimisers, but does work on both SQL2005 and 2008) so is def way to go and will find out more.

Many thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-08 : 04:12:12

Simple copy and paste mistake from my part (04/07/2009 : 09:33:46).
Sorry for the inconvenience.

E 12°55'05.63"
N 56°04'39.26"

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-08 : 10:27:51

I figured it was copy paste problem but I was educating myself on Peso's post and did the simple correction.
My question on this though is how can you use XMLNAMESPACES in this case
<ns1:GSys_ProjRefDetail_MX xmlns:ns1="urn:com.CLIENT.GSys.ProjRef"> when the tag includes two items
ns1:GSys_ProjRefDetail_MX and xmlns:ns1="urn:com.CLIENT.GSys.ProjRef".

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

VonC
Starting Member

6 Posts

Posted - 2009-04-09 : 14:29:30

Yes, odd even though it works. Maybe someone knows if its logical, it seems to fly in the face of it though!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources