Nesting XML from a SQL Query - Problems

L GIST Source

So here is the finished product I am looking for:

<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>

Here is the table Structure

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

Here is the SQL I am using:

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

Here is what is being returned:

<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>

What I do not understand is why the <transaction> tag is not closing after each <defaultKey> tag? I also will need to add the remainder of the tags after reason code, but I am stuck right here. Should I be using Explicit or will PATH work better in this situation? I hate having to do this from SQL but I am not sure how else to get it done easily.

sqlxml

Answers

answered 7 days ago Ryan Hansen #1

First: It looks like you have messed up in setting the parent for the reason element. Your spec indicates that you would like reason to be a child element of the createDispute element. However; your query has it set as a child element of the transaction instead.

To correct that, change the parent value for the query that returns reason from 2 to 1. You can then return amount, pnote, and order from that same query.

Next: You don't need the 5th column (defaultKey!3!) - delete it in all queries.

Then: Change null as 'createDispute!1!' to custno + '_' + cast(invno as varchar(50)) as 'createDispute!1!'. Also code the 3rd returned value for each additional query as custno + '_' + cast(invno as varchar(50)) instead of returning null.

Finally: ORDER BY custno + '_' + cast(invno as varchar(50)). This is the "special sauce" that ties everything together.

That should do it. Happy coding!

TL:DR (refactored):

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

answered 7 days ago Shnugo #2

From your code I assume this is SQL Server. But this is not entirely sure... Hope, that my magic crystal ball is working well. Next time please sepcify the actual RDBMS (vendor and version).

Using FOR XML EXPLICIT is a pain in the neck... This was a good approach centuries ago, today one should prefer FOR XML PATH.

Your goal is easily achieved with this:

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

--The query will create the XML as shown in your finished product

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

The result

<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>

Hint: FOR XML EXPLICIT is still the right choice if one needs CDATA sections or very fance tricks with namespaces.

But I do not understand your comment: I want each row of data in the DB to return a result set.

Might be you are looking for this:

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;

answered 7 days ago Parfait #3

Consider a nested query:

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 Demo

Unfortunately, result here returns the well-known issue of repeated namespaces in the nested element defaultKey, here being an empty one: 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>

comments powered by Disqus