从 SQL 查询嵌套 XML - 问题

Question

所以这是我正在寻找的成品：

<GPAutoActions>
<createDispute>
<transaction>
<defaultKey>
<custNo>10000000-AD</custNo>
<invNo>28893848</invNo>
</defaultKey>
</transaction>
<reason>405</reason>
<amount>185.17</amount>  
<pnote>Notes</pnote>
<owner>LARRYGIST</owner>
</createDispute>
</GPAutoActions>

这是表格结构

custno          invno    reason amount  pnote   owner
117455521-AD    28894882    405 972.04  Note    LARRYGIST
128623268-AD    28887277    405 182.99  Note    LARRYGIST
131537715-AD    28893848    405 185.17  Note    LARRYGIST
189063783-AD    28927273    405 777.49  Note    LARRYGIST

这是我正在使用的 SQL：

Select 1 as TAG
, null as parent
, null as 'createDispute!1!'
, null as 'transaction!2!Element'
, null as 'defaultKey!3!'
, null as 'defaultKey!3!custno!Element'
, null as 'defaultKey!3!InvNo!Element'
, null as 'reason!4!'
UNION ALL
Select 2 as Tag
, 1 as Parent
, Null
, NULL
, null
, null
, null
, null
Union ALL
Select 3 as Tag
, 2 as Parent
, Null
, NULL
, null
, custno
, InvNo
, null
FROM [GetPaid_Sandbox].[dbo].[DisputeData]
Union ALL
Select 4 as Tag
, 2 as Parent
, Null
, NULL
, null
, null
, null
, reason
FROM [GetPaid_Sandbox].[dbo].[DisputeData]
for XML EXPLICIT

以下是要返回的内容：

<createDispute>
<transaction>
<defaultKey>
<custno>117455521-AD</custno>
<InvNo>28894882</InvNo>
</defaultKey>
<defaultKey>
<custno>128623268-AD</custno>
<InvNo>28887277</InvNo>
</defaultKey>
<defaultKey>
<custno>131537715-AD</custno>
<InvNo>28893848</InvNo>
</defaultKey>
<defaultKey>
<custno>189063783-AD</custno>
<InvNo>28927273</InvNo>
</defaultKey>
<reason>405</reason>
<reason>405</reason>
<reason>405</reason>
<reason>405</reason>
</transaction>
</createDispute>

我不明白的是为什么<transaction>标签在每个<defaultKey>标签后没有关闭？我还需要在原因代码之后添加其余的标签，但我被困在这里。我应该使用显式还是在这种情况下 PATH 会更好地工作？我讨厌从SQL中执行此操作，但我不确定如何轻松完成它。

Answer 1

从你的代码中，我假设这是SQL Server。但这并不完全确定...希望，我的魔法水晶球运行良好。下次请确定实际的RDBMS(供应商和版本)。

使用FOR XML EXPLICIT是脖子上的疼痛...这在几个世纪前是一个很好的方法，今天人们应该更喜欢FOR XML PATH.

您的目标很容易实现：

DECLARE @mockup TABLE(custno VARCHAR(100),invno INT,reason INT,amount DECIMAL(10,4),pnote VARCHAR(100),[owner] VARCHAR(100));
INSERT INTO @mockup VALUES
('117455521-AD',28894882,405,972.04,'Note','LARRYGIST')
,('128623268-AD',28887277,405,182.99,'Note','LARRYGIST')
,('131537715-AD',28893848,405,185.17,'Note','LARRYGIST')
,('189063783-AD',28927273,405,777.49,'Note','LARRYGIST');

--查询将创建 XML，如成品所示

SELECT custno AS [transaction/defaultKey/custNo]
,invno AS  [transaction/defaultKey/invNo]
,reason
,amount
,pnote
,[owner]
FROM @mockup 
WHERE invno=28893848
FOR XML PATH('createDispute'),ROOT('GPAutoActions');

结果

<GPAutoActions>
<createDispute>
<transaction>
<defaultKey>
<custNo>131537715-AD</custNo>
<invNo>28893848</invNo>
</defaultKey>
</transaction>
<reason>405</reason>
<amount>185.1700</amount>
<pnote>Note</pnote>
<owner>LARRYGIST</owner>
</createDispute>
</GPAutoActions>

提示：如果需要CDATA部分或带有命名空间的非常花哨的技巧，FOR XML EXPLICIT仍然是正确的选择。

但我不明白你的评论：我希望数据库中的每一行数据都返回一个结果集。

可能您正在寻找这个：

SELECT outerTable.invno
,(
SELECT innerTable.custno AS [transaction/defaultKey/custNo]
,innerTable.invno AS  [transaction/defaultKey/invNo]
,innerTable.reason
,innerTable.amount
,innerTable.pnote
,innerTable.[owner]
FROM @mockup AS innerTable
WHERE innerTable.invno=outerTable.invno
FOR XML PATH('createDispute'),ROOT('GPAutoActions'),TYPE
)
FROM @mockup AS outerTable;

Answer 2

首先：看起来您在为 reason 元素设置父元素时搞砸了。您的规范表明您希望reason成为createDispute元素的子元素。然而;您的查询已将其设置为transaction的子元素。

若要更正此问题，请将返回reason的查询的parent值从2更改为1。然后，您可以从同一查询返回amount、pnote和order。

下一页： 您不需要第 5 列 (defaultKey!3!) - 在所有查询中删除它。

然后：将null as 'createDispute!1!'更改为custno + '_' + cast(invno as varchar(50)) as 'createDispute!1!'。此外，将每个附加查询的第三个返回值编码为custno + '_' + cast(invno as varchar(50))，而不是返回 null。

最后：ORDER BY custno + '_' + cast(invno as varchar(50)).这是将所有东西联系在一起的"特殊酱汁"。

这应该可以做到。祝您编码愉快！

TL：DR(重构)：

Select 1 as TAG
, null as parent
, custno + '_' + cast(invno as varchar(50)) as 'createDispute!1!'
, null as 'transaction!2!reason!Element'
, null as 'transaction!2!amount!Element'
, null as 'transaction!2!pnote!Element'
, null as 'transaction!2!owner!Element'
, null as 'defaultKey!3!custno!Element'
, null as 'defaultKey!3!InvNo!Element'
FROM [GetPaid_Sandbox].[dbo].[DisputeData]
UNION ALL
Select 2 as Tag
, 1 as Parent
, custno + '_' + cast(invno as varchar(50))
, reason
, amount
, pnote
, [owner]
, null
, null
FROM [GetPaid_Sandbox].[dbo].[DisputeData]
Union ALL
Select 3 as Tag
, 2 as Parent
, custno + '_' + cast(invno as varchar(50))
, NULL
, NULL
, NULL
, NULL
, custno
, InvNo
FROM [GetPaid_Sandbox].[dbo].[DisputeData]
Union ALL
ORDER BY custno + '_' + cast(invno as varchar(50))
for XML EXPLICIT

Answer 3

考虑一个嵌套查询：

SELECT 1 as TAG
, null as parent
, (SELECT 1 AS [Tag], NULL AS [Parent], sub.custno AS 'defaultKey!1!custno!Element', sub.invno AS 'defaultKey!1!invno!Element'
FROM DisputeData sub
WHERE d.custno = sub.custno
FOR XML EXPLICIT, TYPE)  AS 'createDispute!1!transaction!Element'
, d.reason as 'createDispute!1!reason!Element'
, d.amount as 'createDispute!1!amount!Element'
, d.pnote as 'createDispute!1!pnote!Element'
, d.owner as 'createDispute!1!owner!Element'
FROM DisputeData As d
FOR XML EXPLICIT, ROOT('GPAutoActions');

Rextester 演示

不幸的是，这里的结果返回了嵌套元素defaultKey中重复命名空间的众所周知的问题，这里是一个空问题：xmlns="".

<?xml version="1.0"?>
<GPAutoActions>
<createDispute>
<transaction>
<defaultKey xmlns="">
<custno>117455521-AD</custno>
<invno>28894882</invno>
</defaultKey>
</transaction>
<reason>405</reason>
<amount>972.04</amount>
<pnote>Dispute Reason: Inbound email from John requesting that account be cancelled, providing letter of cancellation. Attachments: Yes</pnote>
<owner>LARRYGIST</owner>
</createDispute>
<createDispute>
<transaction>
<defaultKey xmlns="">
<custno>128623268-AD</custno>
<invno>28887277</invno>
</defaultKey>
</transaction>
<reason>405</reason>
<amount>182.99</amount>
<pnote>Dispute Reason: Inbound email from Catherine requesting cancelation of services. Attachments: Yes</pnote>
<owner>LARRYGIST</owner>
</createDispute>
<createDispute>
<transaction>
<defaultKey xmlns="">
<custno>131537715-AD</custno>
<invno>28893848</invno>
</defaultKey>
</transaction>
<reason>405</reason>
<amount>185.17</amount>
<pnote>Dispute Reason: Syed stated that he canceled his Tyco contract a long time ago. Syad stated that he doesn't have an email address and didn't want to send CR a cancellation notice.  Attachments: No</pnote>
<owner>LARRYGIST</owner>
</createDispute>
<createDispute>
<transaction>
<defaultKey xmlns="">
<custno>189063783-AD</custno>
<invno>28927273</invno>
</defaultKey>
</transaction>
<reason>405</reason>
<amount>777.49</amount>
<pnote>Dispute Reason: Spoke to Grant stated moved out of building on 12 01 2017. Stated company that bought building decided not to go with Tyco and Tyco came and picked up the equipment. Attachments: No</pnote>
<owner>LARRYGIST</owner>
</createDispute>
</GPAutoActions>