时间戳

首页 > 解决方案 > 从 SQL 查询中嵌套 XML - 问题

sql - 从 SQL 查询中嵌套 XML - 问题

问题描述

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

<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>？我还需要在原因代码之后添加其余的标签，但我被困在这里。我应该使用 Explicit 还是 PATH 在这种情况下会更好地工作？我讨厌必须从 SQL 中执行此操作，但我不确定如何轻松完成它。

标签： sqlxml

解决方案

根据您的代码，我假设这是 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>

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

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

可能你正在寻找这个：

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;

推荐阅读