Generating a correct xpath_namespaces

I want to open a XML document with a xmlns declaration, i open the xml with:
-- Open the XML doc
EXEC sp_xml_preparedocument @.itemHandle OUTPUT, @.xml, '<PriceList xmlns:maxbo="http://maxbo.no/schema/PriceList.xsd" />'

Then i try to select somthing (this returns nothing)
select * from
OPENXML(@.itemHandle, N'/PriceList')

Here are the first few rows.
<?xml version="1.0" encoding="iso-8859-1"?>
<PriceList xmlns="http://maxbo.no/schema/PriceList.xsd">.....

The following code works fine, so how do i generate the xmlns for the above scenario? (it seems obvious to me that the xpath_namespace declartation is somhow faulty)

I want to open a XML document with a xmlns declaration, i open the xml with:
-- Open the XML doc
EXEC sp_xml_preparedocument @.itemHandle OUTPUT, @.xml
Then i try to select somthing (this returns nothing)
select * from
OPENXML(@.itemHandle, N'/PriceList')
Here are the first few rows.
<?xml version="1.0" encoding="iso-8859-1"?>
<PriceList>....

You need to provide the namespace/prefix binding to the third argument as you do above, but you need to use a prefix to bind the namespace URI to AND use it in the query. Default namespaces are unfortunately not picked up.

Try:

EXEC sp_xml_preparedocument @.itemHandle OUTPUT, @.xml, '<PriceList xmlns:maxbo="http://maxbo.no/schema/PriceList.xsd" />'
select * from
OPENXML(@.itemHandle, N'/maxbo:PriceList')

Best regards

Michael

|||

That works, although I have to prefix every item in the list like this:

updateDate varchar(50) '/maxbo:PriceList/maxbo:Update/text()',

But at least it works. If you have an easier way of accessing subitems, I would be gratefull, since some paths can become quite long.