Insert or store a XML created with XML PATH that has namespaces

Question

I generate a XML with XML PATH that has 6 namespaces. There is a schema and therefore I can not change the XML structure.

WITH XMLNAMESPACES ('NS1' AS ns1,
                    'NS2' AS ns2, 
                    'NS3' AS ns3,
                    'NS4' AS ns4,
                    'NS5' AS ns5,
                    'NS6' AS ns6)
SELECT(SELECT 'something' AS 'ns3:node2' FOR XML PATH('ns2:Node1'), TYPE)
FOR XML PATH(''),
ROOT('ns1:RootNode');

Now I need to either insert the output into a tmp table or store it in a variable. The problem I am stuck at is that with needs a ; in front of it. So Set @myVariable = above Codeblock and insert into tmp table values(above codeblock) are both not working and I am wondering if there is a way to store it. The XML is valid and works fine if I look at it or save it to the hard disk, but I need to do some more work with that XML.

Answer 1

You need to put your whole SELECT within a further subquery. So for an insert:

DECLARE @T TABLE (X XML);
WITH XMLNAMESPACES ('NS1' AS ns1,
                    'NS2' AS ns2, 
                    'NS3' AS ns3,
                    'NS4' AS ns4,
                    'NS5' AS ns5,
                    'NS6' AS ns6)
INSERT @T (X)
SELECT  (SELECT (SELECT 'something' AS 'ns3:node2' FOR XML PATH('ns2:Node1'), TYPE)
        FOR XML PATH(''),   
        ROOT('ns1:RootNode'));

SELECT * FROM @T;

Or to assign a variable:

DECLARE @X XML;
WITH XMLNAMESPACES ('NS1' AS ns1,
                    'NS2' AS ns2, 
                    'NS3' AS ns3,
                    'NS4' AS ns4,
                    'NS5' AS ns5,
                    'NS6' AS ns6)
SELECT @X= (SELECT  (SELECT 'something' AS 'ns3:node2' FOR XML PATH('ns2:Node1'), TYPE)
            FOR XML PATH(''),
            ROOT('ns1:RootNode')
            );
SELECT @X;