Sum on XML header

Transact-SQL
1

Hi Everyone,

Good day!

Can I please get some assistance?

I have a query that generates an output in xml.
However, I need to add some summary values on the root node

Below is just a sample:

line1
invoice amount="10"
invoice amount="20"
cnote amount ="30"
pmt amount = "40"
pmt amount = "50"
line1

Where the header line1 is, I need to be able to put in totals for the invoices, cnotes and pmts.
Can anyone please offer any suggestions?

Thank you

2

For starters, post your code and input data

3

Without your code + table structure it is hard to say what the changes you need to make are. Here is an example that might be helpful.

CREATE TABLE #tmp (col1 INT, col2 INT);
INSERT INTO #tmp VALUES (1,4),(5,7);


SELECT
	col1 + col2 AS [@sum],
	col1,
	col2
FROM #tmp
FOR XML PATH ('line1');

DROP TABLE #tmp;
4

Hi.

Apologies for that.

Just need some ideas or perhaps a framework on how to get the summation on the header node.
Here is the basic structure of what I have:

SELECT XmlCol.query(' { /* } ')
FROM (
SELECT
col1, col2,
(
select col1, col2
from table1 t1
where end.colx = t1.colx
for xml auto, type
),

		(	
			select col1, col2
			from table2 t2
			where end.coly = t2.coly
			for xml auto, type
		
	)

	FROM table3 end
	where end.colx 
	FOR XML AUTO,TYPE

) as T(XmlCol)

I am hoping to add some SUM values on the File Summary node
Any suggestions would be appreciated.

Thank you

5

@Kristen format sql :grinning:

and may I also suggest, as @khtan has most replies and :heart: likes, that we can do:
@khtan solve sql

1 Like
6

I would like to help, but looking at the code you posted I don't know what the "File Summary" node is, and what nodes/quantities you are trying to aggregate. If you can post the data in a consumable format (e.g. with create table statements etc. similar to what I had posted in my sample code in my earlier posting), that would make it easier for someone to respond.

7

Hi All,

Thank you for your replies so far.
I do apologize again for the vagueness.

I have below a trimmed down version of my script, which can be run to better illustrate my question:

CREATE TABLE #Debtor (DebtorID char(30), DebtorName char(30))
INSERT INTO #Debtor VALUES ('A1', 'A1 Debtor')

CREATE TABLE #Address (DebtorID char(30), Address char(50), Country char(10))
INSERT INTO #Address VALUES ('A1', 'some address', 'US')

CREATE TABLE #Contact (DebtorID char(30), Contactname char (20))
INSERT INTO #Contact VALUES ('A1', 'First Last')

CREATE TABLE #NewTrx (DebtorID char(30), Type int, Amount numeric(10,2))
INSERT INTO #NewTrx VALUES ('A1', 1, 10)
INSERT INTO #NewTrx VALUES ('A1', 1, 20)
INSERT INTO #NewTrx VALUES ('A1', 2, 30)

CREATE TABLE #OldTrx (DebtorID char(30), Type int, Amount numeric(10,2))
INSERT INTO #OldTrx VALUES ('A1', 1, 40)
INSERT INTO #OldTrx VALUES ('A1', 2, 50)
INSERT INTO #OldTrx VALUES ('A1', 2, 60)

SELECT XmlCol.query(' { /* } ')
FROM
(
SELECT
Rtrim(DebtorID) DebtorID, Rtrim(DebtorName) DebtorName,
(
select
Rtrim(DebtorID) DebtorID,
Rtrim([Address]) [Address],
Rtrim(Country) Country
from #Address
where [Debtor].DebtorID = #Address.DebtorID
for xml auto, type
),

(	
	select 
		Rtrim(DebtorID) DebtorID, 
		Rtrim(Contactname) [Contactname]
	from #Contact
	where [Debtor].DebtorID = #Contact.DebtorID
	for xml auto, type
),

(
	select 
		Rtrim(DebtorID) DebtorID, 
		case [Type] 
		when 1 then 'Invoice' 
		when 2 then 'Payment' 
		end as [DocType], 
		Amount
	from #NewTrx
	where [Debtor].DebtorID = #NewTrx.DebtorID
	for xml auto, type
),

(
	select 
		Rtrim(DebtorID) DebtorID, 
		case [Type] 
		when 1 then 'Invoice' 
		when 2 then 'Payment' 
		end as [DocType], 
		Amount
	from #OldTrx
	where [Debtor].DebtorID = #OldTrx.DebtorID
	for xml auto, type

)

FROM #Debtor [Debtor]
FOR XML AUTO,TYPE
) as T(XmlCol)

Drop Table #Debtor
Drop Table #Address
Drop Table #Contact
Drop Table #NewTrx
Drop Table #OldTrx

The resulting XML file looks like:

FileSummary
Debtor DebtorID="A1" DebtorName="A1 Debtor"
_x0023_Address DebtorID="A1" Address="some address" Country="US" /
_x0023_Contact DebtorID="A1" Contactname="First Last" /
_x0023_NewTrx DebtorID="A1" DocType="Invoice" Amount="10.00" /
_x0023_NewTrx DebtorID="A1" DocType="Invoice" Amount="20.00" /
_x0023_NewTrx DebtorID="A1" DocType="Payment" Amount="30.00" /
_x0023_OldTrx DebtorID="A1" DocType="Invoice" Amount="40.00" /
_x0023_OldTrx DebtorID="A1" DocType="Payment" Amount="50.00" /
_x0023_OldTrx DebtorID="A1" DocType="Payment" Amount="60.00" /
/Debtor
/FileSummary

At this stage, I need assistance to populate the FileSummary node above with a summation for invoices and payments.

I hope that makes sense?

Any assistance would be appreciated.

Thank you

8

Hi All,

I think I managed to get what I needed with the below change:

XmlCol.query('FileSummary
grandTotal="{sum(Debtor///@TransactionTotal)}"
Invoices="{sum(Debtor/[@TransactionType="Invoice"]/@TransactionTotal)}"
Payments="{sum(Debtor/[@TransactionType="Payment"]/@TransactionTotal)}"
{ / } /FileSummary')

Thank you all for your guidance.

I appreciate your assistance