September 28, 2011 at 11:07 am
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
September 28, 2011 at 12:07 pm
September 29, 2011 at 2:38 am
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.
September 29, 2011 at 6:50 am
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
September 29, 2011 at 8:22 am
Drew,
Thanks very much for the explanation - moved my learning on a bit
Laurie
September 29, 2011 at 9:43 am
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:
<i xmlns="swp" xmlns:swp="http://swp.converteam.com" n="T12345" />
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:
<i xmlns="http://swp.converteam.com" n="T12345" />
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.
October 4, 2011 at 10:15 am
You are completely right and thank you for your clarification.
L
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy