Append an XML Nodes to existing Tree in SQL

Question

I have an SQL query that generates the result as an XML column type. I want to alter that query so that it generates below output by appending it to the header/beginning:

EXPECTED OUTPUT:

<Root>
   <Header>
      <MessageID>044</MessageID>
      <TotalNumberOfTransactionsDeclared>1047</TotalNumberOfTransactionsDeclared>
      <TotalAmountWithoutVAT>25771.79</TotalAmountWithoutVAT>
   </Header>
   <Entities>
      <Entity>
         <CustomerVATID>100050301900003</CustomerVATID>
         <Leases>
            <Lease>
               <LeaseIdentifier>4374</LeaseIdentifier>
               <Invoices>
                  <Invoice>
                    ...
                  </Invoice>
               </Invoices>
            </Lease>
         </Leases>
      </Entity>
   </Entities>
</Root>

Now whatever nodes coming after invoices I was able to get the result. However, am unable to add the root nodes before <Invoices> tag. How can I do that?

Please provide sample data and expected output. Strive for a minimal, reproducable example. You probably do not need 10 fields in order to reproduce your issue. — Sander
– Sander, Commented Sep 20, 2020 at 11:53
@Sander done, my query will only return all the nodes structure between <Invoices> <Invoice> </Invoice> <Invoices> — Mohd
– Mohd, Commented Sep 20, 2020 at 12:01

Sander · Accepted Answer · 2020-09-20 16:01:49Z

If you want full control over your XML output, then you could go with the explicit and element options. It is a lot of work, but you can define every tag and value.

How `EXPLICIT` and `ELEMENT` work

Sample data

create table MyCustomers
(
  CustomerId int,
  Name nvarchar(10)
);

insert into MyCustomers (CustomerId, Name) values
(1, 'Alfred'),
(2, 'Batman');

create table MyInvoices
(
  CustomerId int,
  OrderId int,
  Amount money
);

insert into MyInvoices (CustomerId, OrderId, Amount) values
(1, 101, 12.34),
(1, 102, 56.78),
(2, 201, 90.12);

Solution

This quickly gets very long, even for this simple example.

-- customer 1 invoice XML
declare @CustomerId int = 1;

--root
select 1 as Tag,
       0 as Parent,
       null as [Root!1],
       null as [Header!2],
       null as [Customer!3!Id],
       null as [Customer!3!Name!ELEMENT],
       null as [Customer!3!InvoiceCount!ELEMENT],
       null as [Invoices!4],
       null as [Invoice!5!OrderId!ELEMENT],
       null as [Invoice!5!Amount!ELEMENT]

  union all

--root/header
select 2,
       1,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null

  union all

--root/header/customer
select 3,
       2,
       null,
       null,
       mc.CustomerId,
       mc.Name,
       count(1) as InvoiceCount,
       null,
       null,
       null
from MyCustomers mc
left join MyInvoices mi
  on mi.CustomerId = mc.CustomerId
where mc.CustomerId = @CustomerId
group by mc.CustomerId, mc.Name

  union all

--root/invoices
select 4,
       1,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null

  union all

--root/invoices/invoice
select 5,
       4,
       null,
       null,
       mi.CustomerId,
       null,
       null,
       null,
       mi.OrderId,
       mi.Amount
from MyInvoices mi
where mi.CustomerId = @CustomerId

for xml explicit;

Result

<Root><Header><Customer Id="1"><Name>Alfred</Name><InvoiceCount>2</InvoiceCount></Customer></Header><Invoices><Invoice><OrderId>101</OrderId><Amount>12.3400</Amount></Invoice><Invoice><OrderId>102</OrderId><Amount>56.7800</Amount></Invoice></Invoices></Root>

Or with some formatting

<Root>
   <Header>
      <Customer Id="1">
         <Name>Alfred</Name>
         <InvoiceCount>2</InvoiceCount>
      </Customer>
   </Header>
   <Invoices>
      <Invoice>
         <OrderId>101</OrderId>
         <Amount>12.3400</Amount>
      </Invoice>
      <Invoice>
         <OrderId>102</OrderId>
         <Amount>56.7800</Amount>
      </Invoice>
   </Invoices>
</Root>

See it in action: fiddle.

Building the expected result

Solution

This solution example reproduces most of your expected output. I used all constant values to keep this simple. There are additional comments to hightlight the structure.

--root
select 1 as Tag, -- 1 = root
       0 as Parent,
       null as [Root!1], -- 1
       null as [Header!2!MessageId!ELEMENT],
       null as [Header!2!TotalNumberOfTransactionsDeclared!ELEMENT],
       null as [Entities!3],
       null as [Entity!4!CustomerVATId!ELEMENT],
       null as [Leases!5],
       null as [Lease!6!LeaseIdentifier!ELEMENT],
       null as [Invoices!7],
       null as [Invoice!8!OrderId!ELEMENT]

  union all

--root/header
select 2, -- 2 = header
       1,
       null,
       '044',  -- 2!MessageID
       '1047', -- 2!TotalNumberOfTransactionsDeclared
       null, null, null, null, null, null

  union all

--root/entities
select 3, -- 3 = entities
       1,
       null, null, null,
       null, -- 3
       null, null, null, null, null

  union all

--root/entities/entity
select 4, -- 4 = entity
       3,
       null, null, null, null,
       '100050301900003', -- 4!CustomerVATId
       null, null, null, null

  union all

--root/entities/entity/leases
select 5, -- 5 = leases
       4,
       null, null, null, null, null,
       null, -- 5
       null, null, null

  union all

--root/entities/entity/leases/lease
select 6, -- 6 = lease
       5,
       null, null, null, null, null, null,
       '4374', -- 6!LeaseIdentifier
       null, null

  union all

--root/entities/entity/leases/invoices
select 7, -- 7 = invoices
       6,
       null, null, null, null, null, null, null,
       null, -- 7
       null
       
  union all

--root/entities/entity/leases/invoices/invoice
select 8, -- 8 = invoices
       7,
       null, null, null, null, null, null, null, null,
       'INV0001' -- 8!OrderId

for xml explicit;

Result

<Root><Header><MessageId>044</MessageId><TotalNumberOfTransactionsDeclared>1047</TotalNumberOfTransactionsDeclared></Header><Entities><Entity><CustomerVATId>100050301900003</CustomerVATId><Leases><Lease><LeaseIdentifier>4374</LeaseIdentifier><Invoices><Invoice><OrderId>INV0001</OrderId></Invoice></Invoices></Lease></Leases></Entity></Entities></Root>

After formatting:

<Root>
   <Header>
      <MessageId>044</MessageId>
      <TotalNumberOfTransactionsDeclared>1047</TotalNumberOfTransactionsDeclared>
   </Header>
   <Entities>
      <Entity>
         <CustomerVATId>100050301900003</CustomerVATId>
         <Leases>
            <Lease>
               <LeaseIdentifier>4374</LeaseIdentifier>
               <Invoices>
                  <Invoice>
                     <OrderId>INV0001</OrderId>
                  </Invoice>
               </Invoices>
            </Lease>
         </Leases>
      </Entity>
   </Entities>
</Root>

Thanks for the input here, but I can't see the following in the output:
<Entities> <Entity> <CustomerVATID>100050301900003</CustomerVATID> <Leases> <Lease> <LeaseIdentifier>4374</LeaseIdentifier>
The answer tells you how you could achieve your desired result. It is not a copy-and-paste ready solution... I expanded my answer with a second section showing how you can get started.
thanks a lot for your input, I have learned a new concept to manipulate XML formatting :)

lptr · Accepted Answer · 2020-09-20 13:46:25Z

0

..the easy way out..

with x as 
( 
    select top (10) * from sys.all_objects
    union all
    (select top (10) * from sys.all_objects order by object_id desc)
)

select 

--header, aggregate
(
    select 
        '1234' as '*',
        count(*) as 'TotalNumberOfTransactionsDeclared', 
        sum(abs(object_id/1000000.)) as 'TotalAmountWithoutVAT'
    from x
    for xml path('Header'), type
),

--leases --get each lease type
(
select dx.type_desc as 'LeaseIdentifier',

    --invoice for each lease
    (
        select *
        from x
        where x.type_desc = dx.type_desc
        FOR XML PATH ('Invoice'), root ('Invoices'), type -- type!!
    )
from x as dx
group by dx.type_desc
for xml path('Lease'), root('Leases'), type
)

for xml path(''), root('ROOT');



select 
(
select top (10) *
from sys.all_objects
FOR XML PATH ('Invoice'), root ('Invoices'), type -- type!!
)
for xml path('ROOT');

-- ...or...
select 
(
select top (10) *
from sys.all_objects
FOR XML PATH ('Invoice'), root ('Invoices'), type -- type!!
)
for xml path(''), root('ROOT');

edited Sep 20, 2020 at 13:46

answered Sep 20, 2020 at 12:56

lptr

6,8582 gold badges10 silver badges16 bronze badges

1 Comment

Mohd Over a year ago

@lptrI Thanks but I can't see the following tags in the output <Entities> <Entity> <CustomerVATID>

Collectives™ on Stack Overflow

Append an XML Nodes to existing Tree in SQL

2 Answers 2

How `EXPLICIT` and `ELEMENT` work

Building the expected result

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

How EXPLICIT and ELEMENT work

Building the expected result

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related

How `EXPLICIT` and `ELEMENT` work