T-SQL - 将具有 XML 输出（包括命名空间）的查询保存到表或变量中

发布于 2025-01-18 10:48:36 字数 719 浏览 3 评论 0原文

我想将下面查询的输出保存到变量或表中。但是，它给出了一个错误，因为查询以“使用命名空间...”开头，

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') ;

我试图将其存储到一个类似的变量中：

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

我还试图将其插入表中。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

绅士风度i 2025-01-25 10:48:36

您应该分配一个表达式，在选择周围使用括号

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;

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;

回复收藏 0 原文

~没有更多了~