MS SQL XML - use sql:variable to declare xml namespaces in stored procedures

Author

use sql:variable to declare xml namespaces in stored procedures

sql developer

2005-11-03, 8:27 pm

Is there a way to use sql:variable to declare xml namespaces? For example:

declare @namespace as nvarchar(50);
set @namespace = "microsoft.com"
select
@validXml.query('
declare default elemant namespace ''sql:variable("@namespace")";
.')

basically I would like to parameterized the value of the default namespace
in the xquery. But, I can not get it to work. Can some one please help me?

Thanks

Kent Tegels

2005-11-03, 8:27 pm

> Is there a way to use sql:variable to declare xml namespaces? For
> example:
> basically I would like to parameterized the value of the default
> namespace in the xquery. But, I can not get it to work. Can some one
> please help me?

I don't believe so. The closest I've gotten is:
declare @ns nvarchar(max)
set @ns = 'microsoft'
declare @x xml
set @x = '<?xml version="1.0" ?><m:a xmlns:m="microsoft"><m:b>1</m:b></m:a>'
select @x.query('declare namespace ms=''{sql:variable("@ns")}'';(/ms:a/ms:b)')

Which compiles but returns nothing. Note that if you remove the sql:variable
subsitution, the query works.

Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Michael Rys [MSFT]

2005-11-04, 3:23 am

The XQuery standard does not allow to parameterize the namespace declaration
with an expression.

In SQL Server, you will have to use dynamic SQL to parameterize it.

Best regards
Michael

PS: The expression below compiles since we accept any string as a namespace
URI. So you are searching for the elements in a namespace
& #123;sql:variable('@
ns')} which is not in the document.

"Kent Tegels" <ktegels@develop.com> wrote in message
news:b87ad7450788c7a
ec3f2770104@news.microsoft.com...
>
> I don't believe so. The closest I've gotten is:
> declare @ns nvarchar(max)
> set @ns = 'microsoft'
> declare @x xml
> set @x = '<?xml version="1.0" ?><m:a
> xmlns:m="microsoft"><m:b>1</m:b></m:a>'
> select @x.query('declare namespace
> ms=''{sql:variable("@ns")}'';(/ms:a/ms:b)')
>
> Which compiles but returns nothing. Note that if you remove the
> sql:variable subsitution, the query works.
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>

Dan Sullivan

2005-11-04, 9:23 am

Somewhat tedious, and possibly slow, way to paramaterize a namespace in in
a T-SQL variable for an XQuery query if you must:

DECLARE @x XML;
SET @x = '<a xmlns="microsoft"><b/></a>';
DECLARE @ns NVARCHAR(MAX);
SET @ns = 'microsoft'
select @x.query('
(/*[local-name()="a" and namespace-uri()=sql:variable("@ns")]
/*[local-name()="b" and namespace-uri()=sql:variable("@ns")])
')

Unfortunately SQL Server does not support let, it it did this would take
less typing:

DECLARE @x XML;
SET @x = '<a xmlns="microsoft"><b/></a>';
DECLARE @ns NVARCHAR(MAX);
SET @ns = 'microsoft'
select @x.query(
let $ns := sql:variable("@ns")
return
/*[local-name()="a" and namespace-uri()=$ns]
/*[local-name()="b" and namespace-uri()=$ns]
)

Dan

Hello sql,

> Is there a way to use sql:variable to declare xml namespaces? For
> example:
>
> declare @namespace as nvarchar(50);
> set @namespace = "microsoft.com"
> select
> @validXml.query('
> declare default elemant namespace ''sql:variable("@namespace")";
> .')
> basically I would like to parameterized the value of the default
> namespace in the xquery. But, I can not get it to work. Can some one
> please help me?
>
> Thanks
>

Sponsored Links