T-SQL - Save query with XML output including namespace into table or variable

Question

I would like to save the output from the query below into a variable or a table. However it gives an error because the query starts with "With namespace ..."

WITH XMLNAMESPACES (
'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as cac
, 'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as cbc
)
select  
'123456''cbc:ID'
, '20222903'   'cbc:IssueDate'
, 'Supplier name' 'cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name'
, ( select
'1' 'cac:Line'
, '3' 'cac:Quantity'
, '120.80' 'cac:LineTotal'
, '8594' 'cac:ItemCode'  
for xml path ('cac:InvoiceLine' ) , type
)
for xml path  ('') , type , root ('Invoice') ;

I tried to store into an variable like:

declare @content xml = WITH XMLNAMESPACES ......... ;

And I also tried to insert into a table.

Answer 1

You should assign an expression, use brackets around the select

declare @x xml;
WITH XMLNAMESPACES (
'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as cac
, 'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as cbc
)
select @x= (select
'123456''cbc:ID'
, '20222903'   'cbc:IssueDate'
, 'Supplier name' 'cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name'
, ( select
'1' 'cac:Line'
, '3' 'cac:Quantity'
, '120.80' 'cac:LineTotal'
, '8594' 'cac:ItemCode'  
for xml path ('cac:InvoiceLine' ) , type
)
for xml path  ('') , type , root ('Invoice') 
);

select @x;