Syntax problem: WITH XMLNAMESPACES in subquery

Question

Post reply

Syntax problem: WITH XMLNAMESPACES in subquery

Laurie Burrow

SSC-Addicted

Points: 424
More actions
September 28, 2011 at 11:07 am

#248984

I need to create dynamically XML including namespace declaration at the top level
As a start I have a TSQL code fragment
WITH XMLNAMESPACES ('http://swp.converteam.com' as swp,DEFAULT 'swp')
SELECT 'T12345' as "@n" FOR XML PATH('i'), TYPE
This returns the expected XML value of
<i xmlns="swp" xmlns:swp="http://swp.converteam.com" n="T12345" />
However the code fragment to allow me to execute the query dynamically
declare @ChildPrtID nvarchar(100) = 'T12345';
SET @xml = (
WITH XMLNAMESPACES ('http://swp.converteam.com' as swp,DEFAULT 'swp')
SELECT @ChildPrtID as "@n" FOR XML PATH('i'), TYPE)
Gives syntax errors
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.
The simpler query
SET @xml = (
WITH XMLNAMESPACES ('http://swp.converteam.com' as swp,DEFAULT 'swp')
SELECT 'T12345' as "@n" FOR XML PATH('i'), TYPE)
Gives similar syntax errors
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
Variations on a similar theme all give similar errors
Example 1
SET @xml = (
;WITH XMLNAMESPACES ('http://swp.converteam.com' as swp,DEFAULT 'swp')
SELECT 'T12345' as "@n" FOR XML PATH('i'), TYPE)
Gives syntax errors
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
Example 2 using a common table expression
with myCTE(att) as
(
SELECT 'T12345' as "@n" FOR XML PATH('i'), TYPE
)
, XMLNAMESPACES ('http://swp.converteam.com' as swp,DEFAULT 'swp')
SELECT att from myCTE
Gives syntax error
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'http://swp.converteam.com'.
Example 3
with myCTE(att) as
(
SELECT 'T12345' as "@n" FOR XML PATH('i'), TYPE
)
,WITH XMLNAMESPACES ('http://swp.converteam.com' as swp,DEFAULT 'swp')
SELECT att from myCTE
Give syntax errors
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Example 4
with myCTE(att) as
(
SELECT 'T12345' as "@n" FOR XML PATH('i'), TYPE
)
;WITH XMLNAMESPACES ('http://swp.converteam.com' as swp,DEFAULT 'swp')
SELECT att from myCTE
Gives syntax errors
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
I have exhausted my imagination, Google and BOL as to how to resolve this. Can any one help?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

vince_sql Right there with Babe Points: 722 More actions · Answer 1

Afaik you must have a select statement immediately after a CTE.

http://sqlvince.blogspot.com/[/url]

Laurie Burrow SSC-Addicted Points: 424 More actions · Answer 2

Thanks Grasshopper, you gave me an idea that resolved part of the problem. I changed the order of the 'with' clauses and this basic query compiles and works correctly

with XMLNAMESPACES ('http://swp.converteam.com' as swp,DEFAULT 'swp') , myCTE(att) as

(

SELECT @ChildPrtID as "@n" FOR XML PATH('i'), TYPE

)

SELECT att from myCTE

However I still have a problem with the dynamic TSQL using subquery to assign value to a variable

This query does not compile

declare @xml XML='';

declare @ChildPrtID nvarchar(100) = 'T12345';

SET @xml = (

;with XMLNAMESPACES ('http://swp.converteam.com' as swp,DEFAULT 'swp') , myCTE(att) as

(

SELECT @ChildPrtID as "@n" FOR XML PATH('i'), TYPE

)

SELECT att from myCTE

)

gives syntax errors

The problem seems to be the 'with' clause is not acceptable inside the subquery.

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near ';'.

Msg 102, Level 15, State 1, Line 9

Incorrect syntax near ')'.

Removing the ';' gives a different set of syntax errors.

drew.allen SSC Guru Points: 76987 More actions · Answer 3

Variables can be set using either a SET statement or a SELECT statement. You need to use the SELECT statement version

declare @xml XML='';

declare @ChildPrtID nvarchar(100) = 'T12345';

;with XMLNAMESPACES ('http://swp.converteam.com' as swp,DEFAULT 'swp')

SELECT @xml = (

SELECT @ChildPrtID as "@n" FOR XML PATH('i'), TYPE

)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Laurie Burrow SSC-Addicted Points: 424 More actions · Answer 4

Drew,

Thanks very much for the explanation - moved my learning on a bit

Laurie

R.P.Rozema SSChampion Points: 12317 More actions · Answer 5

Apart from the T-SQL syntax errors that Drew gave you a solution for, you also seem to have a misconception on the use of namespaces in xml.

The xml document you generated:

has an element i and an attribute n, both in namespace "swp". Next to that a namespace "http://swp.converteam.com" is declared but never used.

What you probably intended is for the i element and n attribute to be in namespace "http://swp.converteam.com". Like this:

or like this:

<swp:i xmlns:swp="http://swp.converteam.com" swp:n="T12345" />

both represent the exact same document, with i and n defined in the "http://swp.converteam.com" namespace.

The T-SQL code to accomplish the first output is:

declare @ChildPrtID nvarchar(100) = 'T12345';

declare @xml xml;

with XMLNAMESPACES (

default 'http://swp.converteam.com'

)

select @xml = (

SELECT @ChildPrtID as [@n]

FOR XML PATH('i'), TYPE

);

select @xml;

The second output is created like this:

declare @ChildPrtID nvarchar(100) = 'T12345';

declare @xml xml;

with XMLNAMESPACES (

'http://swp.converteam.com' as swp

)

select @xml = (

SELECT @ChildPrtID as [@swp:n]

FOR XML PATH('swp:i'), TYPE

);

select @xml;

As said, these both documents are completely equivalent and are probably what you intended to create.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Laurie Burrow SSC-Addicted Points: 424 More actions · Answer 6

You are completely right and thank you for your clarification.

L