SQL Anywhere database - How to use XML with default namespace?

Author

How to use XML with default namespace?

Jarek

2005-05-18, 3:23 am

I need to use openxml function with XML that contains namespace declaration
in format: xmlns="something".
The problem is that when I have declaration xmlns="something" in XML, then
the select below does not return anything:

SELECT * FROM OPENXML( '<products xmlns="something">
<prod_type id="301">Tee Shirt</prod_type>
<prod_type id="401">Baseball Cap</prod_type>
</products>',
'//prod_type',1)
WITH ( prod_name LONG VARCHAR 'text()',
prod_id CHAR(3) '@id')

Result: (empty)

but when I remove xmlns="something" or change it to something like
xmlns:a="something" it works fine:

SELECT * FROM OPENXML( '<products xmlns:a="something">
<prod_type id="301">Tee Shirt</prod_type>
<prod_type id="401">Baseball Cap</prod_type>
</products>',
'//prod_type',1)
WITH ( prod_name LONG VARCHAR 'text()',
prod_id CHAR(3) '@id')

Result:

prod_name prod_id
'Tee Shirt' '301'
'Baseball Cap' '401'

I've tried to set namespace-declaration argument of openxml function but
don't know what is the format of this declaration(nothing in manual).
How to use openxml function to get right result from XML with such a
namespace declaration?

Jarek

Breck Carter [TeamSybase]

2005-05-18, 7:23 am

Here is a working example (with one line "wrapped")...

SELECT search_results.*
FROM Alexa ( Query = '"godzilla"', TimeOut = 10000, Count = 100 ),
LATERAL (
OPENXML (
Alexa.Value,

'/SearchResponse/SearchResult/Alexa/WebSearch/Results/Result',
1,
'<z
xmlns="http://webservices.amazon.com/AWSAlexa/2004-09-15">z</z>' )
WITH (
dataurl VARCHAR ( 1000 ) 'DataUrl[@xmlns:type="navigable"]',
title VARCHAR ( 1000 ) 'Title',
score INTEGER 'Score',
context VARCHAR ( 1000 ) 'Context' ) )
AS search_results
WHERE Alexa.Attribute = 'Body';

Breck

On 18 May 2005 01:30:13 -0700, "Jarek" <jtl@skg.pl> wrote:

>I need to use openxml function with XML that contains namespace declaration
>in format: xmlns="something".
>The problem is that when I have declaration xmlns="something" in XML, then
>the select below does not return anything:
>
>SELECT * FROM OPENXML( '<products xmlns="something">
> <prod_type id="301">Tee Shirt</prod_type>
> <prod_type id="401">Baseball Cap</prod_type>
> </products>',
> '//prod_type',1)
>WITH ( prod_name LONG VARCHAR 'text()',
>prod_id CHAR(3) '@id')
>
>Result: (empty)
>
>but when I remove xmlns="something" or change it to something like
>xmlns:a="something" it works fine:
>
>SELECT * FROM OPENXML( '<products xmlns:a="something">
> <prod_type id="301">Tee Shirt</prod_type>
> <prod_type id="401">Baseball Cap</prod_type>
> </products>',
> '//prod_type',1)
>WITH ( prod_name LONG VARCHAR 'text()',
>prod_id CHAR(3) '@id')
>
>Result:
>
>prod_name prod_id
>'Tee Shirt' '301'
>'Baseball Cap' '401'
>
>I've tried to set namespace-declaration argument of openxml function but
>don't know what is the format of this declaration(nothing in manual).
>How to use openxml function to get right result from XML with such a
>namespace declaration?
>
>Jarek
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Sponsored Links