I have the following data and I want to generate XML where the transactions are nested under the position for the relevant asset.
CREATE TABLE #temptable (
[bp_sym] varchar(50),
[person_key] varchar(100),
[bargain_date] date,
[disp_acqu_text] varchar(9),
[quantity] decimal(12,2),
[cumulative_qty] decimal(12,2),
[book_cost_change] decimal(12,2),
[cumulative_book_cost] decimal(12,2),
[swim_refs] varchar(6),
[security_code] char(7),
[asset_name] nvarchar(4000) )
INSERT INTO #temptable ([bp_sym], [person_key], [bargain_date], [disp_acqu_text], [quantity], [cumulative_qty], [book_cost_change], [cumulative_book_cost], [swim_refs], [security_code], [asset_name])
VALUES
( 'AJ.9221', 'P.1000', N'2019-04-23T00:00:00', 'Disposal', 0.00, 1250.00, -19.25, 12839.65, 'uHp810', '2676302', N'Goldcorp Inc Com NPV (Home Quote)' ),
( 'AJ.9221', 'P.1000', N'2019-04-23T00:00:00', 'Disposal', -1250.00, 0.00, -12839.65, 0.00, 'uHp811', '2676302', N'Goldcorp Inc Com NPV (Home Quote)' ),
( 'AJ.9221', 'P.1000', N'2019-04-23T00:00:00', 'Acquistn', 410.00, 410.00, 12839.65, 12839.65, 'OHa904', 'BJYKTV2', N'NEWMONT CORPORATION COM USD1.60' ),
( 'AJ.9221', 'P.1000', N'2019-07-01T00:00:00', 'Sale', -30000.00, 0.00, -11259.12, 0.00, 'SK4057', 'B1G9T99', N'GOLDEN PROSPECT PRECIOUS METALS LTD ORD GBP0.001' ),
( 'AJ.9221', 'P.1000', N'2019-07-02T00:00:00', 'Sale', -300.00, 1100.00, -236.91, 868.67, 'SK5258', 'BF7MPL9', N'SSR MINING INC COM NPV' ),
( 'AJ.9221', 'P.1000', N'2019-07-02T00:00:00', 'Sale', -2700.00, 4800.00, -1719.64, 3057.14, 'SK5239', 'BFZ2TF3', N'MAYA GOLD & SILVER INC COM NPV' ),
( 'AJ.9221', 'P.1000', N'2019-10-25T00:00:00', 'Sale', -40.00, 370.00, -1252.65, 11587.00, 'SR7868', 'BJYKTV2', N'NEWMONT CORPORATION COM USD1.60' )
declare @person_key varchar(10) = 'P.1000', @start_date date = '2019-01-01', @end_date date = '2020-06-30'
select Trx2.asset_name as [@assetName], Trx2.security_code as [@assetSedol], Trx2.security_code as [@ref],
(select Trx1.security_code as assetSedol, Trx1.asset_name as assetName, trx1.quantity, Trx1.bargain_date as bargainDate, Trx1.disp_acqu_text as trxType,
Trx1.cumulative_qty as cumQuantity, Trx1.book_cost_change as chgBookCost, Trx1.cumulative_book_cost as cumBookCost, Trx1.swim_refs as trxRef
from #temptable Trx1
where Trx1.person_key = @person_key
and Trx1.bargain_date between @start_date and @end_date
and Trx1.swim_refs = Trx2.swim_refs
for xml path ('trx'), type)
from #temptable Trx2
where Trx2.person_key = @person_key
and Trx2.bargain_date between @start_date and @end_date
for xml path ('position'), root ('dspslTrxSched'), type
DROP TABLE #temptable
My query is nearly right, but I am getting separate <position> nodes for the two transactions for Goldcorp and I want them to be nested under a single position (see below). I've tried several variants of the query without getting any further. What am I missing? TIA
<dspslTrxSched>
<position assetName="Goldcorp Inc Com NPV (Home Quote)" assetSedol="2676302" ref="2676302">
<trx>
<assetSedol>2676302</assetSedol>
<assetName>Goldcorp Inc Com NPV (Home Quote)</assetName>
<quantity>0.00</quantity>
<bargainDate>2019-04-23</bargainDate>
<trxType>Disposal</trxType>
<cumQuantity>1250.00</cumQuantity>
<chgBookCost>-19.25</chgBookCost>
<cumBookCost>12839.65</cumBookCost>
<trxRef>uHp810</trxRef>
</trx>
</position>
<position assetName="Goldcorp Inc Com NPV (Home Quote)" assetSedol="2676302" ref="2676302">
<trx>
<assetSedol>2676302</assetSedol>
<assetName>Goldcorp Inc Com NPV (Home Quote)</assetName>
<quantity>-1250.00</quantity>
<bargainDate>2019-04-23</bargainDate>
<trxType>Disposal</trxType>
<cumQuantity>0.00</cumQuantity>
<chgBookCost>-12839.65</chgBookCost>
<cumBookCost>0.00</cumBookCost>
<trxRef>uHp811</trxRef>
</trx>
</position>
<position assetName="NEWMONT CORPORATION COM USD1.60" assetSedol="BJYKTV2" ref="BJYKTV2">
<trx>
<assetSedol>BJYKTV2</assetSedol>
<assetName>NEWMONT CORPORATION COM USD1.60</assetName>
<quantity>410.00</quantity>
<bargainDate>2019-04-23</bargainDate>
<trxType>Acquistn</trxType>
<cumQuantity>410.00</cumQuantity>
<chgBookCost>12839.65</chgBookCost>
<cumBookCost>12839.65</cumBookCost>
<trxRef>OHa904</trxRef>
</trx>
</position>
<position assetName="GOLDEN PROSPECT PRECIOUS METALS LTD ORD GBP0.001" assetSedol="B1G9T99" ref="B1G9T99">
<trx>
<assetSedol>B1G9T99</assetSedol>
<assetName>GOLDEN PROSPECT PRECIOUS METALS LTD ORD GBP0.001</assetName>
<quantity>-30000.00</quantity>
<bargainDate>2019-07-01</bargainDate>
<trxType>Sale</trxType>
<cumQuantity>0.00</cumQuantity>
<chgBookCost>-11259.12</chgBookCost>
<cumBookCost>0.00</cumBookCost>
<trxRef>SK4057</trxRef>
</trx>
</position>
<position assetName="SSR MINING INC COM NPV" assetSedol="BF7MPL9" ref="BF7MPL9">
<trx>
<assetSedol>BF7MPL9</assetSedol>
<assetName>SSR MINING INC COM NPV</assetName>
<quantity>-300.00</quantity>
<bargainDate>2019-07-02</bargainDate>
<trxType>Sale</trxType>
<cumQuantity>1100.00</cumQuantity>
<chgBookCost>-236.91</chgBookCost>
<cumBookCost>868.67</cumBookCost>
<trxRef>SK5258</trxRef>
</trx>
</position>
<position assetName="MAYA GOLD & SILVER INC COM NPV" assetSedol="BFZ2TF3" ref="BFZ2TF3">
<trx>
<assetSedol>BFZ2TF3</assetSedol>
<assetName>MAYA GOLD & SILVER INC COM NPV</assetName>
<quantity>-2700.00</quantity>
<bargainDate>2019-07-02</bargainDate>
<trxType>Sale</trxType>
<cumQuantity>4800.00</cumQuantity>
<chgBookCost>-1719.64</chgBookCost>
<cumBookCost>3057.14</cumBookCost>
<trxRef>SK5239</trxRef>
</trx>
</position>
<position assetName="NEWMONT CORPORATION COM USD1.60" assetSedol="BJYKTV2" ref="BJYKTV2">
<trx>
<assetSedol>BJYKTV2</assetSedol>
<assetName>NEWMONT CORPORATION COM USD1.60</assetName>
<quantity>-40.00</quantity>
<bargainDate>2019-10-25</bargainDate>
<trxType>Sale</trxType>
<cumQuantity>370.00</cumQuantity>
<chgBookCost>-1252.65</chgBookCost>
<cumBookCost>11587.00</cumBookCost>
<trxRef>SR7868</trxRef>
</trx>
</position>
</dspslTrxSched>
I think this would work best if you had a separate table already that had a list of asset_name and security_code values, but it looks like all you might need to do is change how the subquery is correlated, use asset_name and security_code instead of swim_refs, which is too unique:
declare @person_key varchar(10) = 'P.1000', @start_date date = '2019-01-01', @end_date date = '2020-06-30';
with position_list as
(select distinct asset_name, security_code
from #temptable
where person_key = @person_key
and bargain_date between @start_date and @end_date)
select Trx2.asset_name as [@assetName], Trx2.security_code as [@assetSedol], Trx2.security_code as [@ref] ,
(select Trx1.security_code as assetSedol, Trx1.asset_name as assetName, trx1.quantity, Trx1.bargain_date as bargainDate, Trx1.disp_acqu_text as trxType,
Trx1.cumulative_qty as cumQuantity, Trx1.book_cost_change as chgBookCost, Trx1.cumulative_book_cost as cumBookCost, Trx1.swim_refs as trxRef
from #temptable Trx1
where Trx1.person_key = @person_key
and Trx1.bargain_date between @start_date and @end_date
and Trx1.asset_name = Trx2.asset_name and Trx1.security_code = Trx2.security_code
for xml path ('trx'), type)
from position_list Trx2
for xml path ('position'), root ('dspslTrxSched'), type
July 23, 2020 at 2:24 pm
Thanks. That gives exactly what I need.
July 23, 2020 at 2:25 pm
ugh, I edited it and made it worse, so I went back to my original post, sorry for any confusion, but glad you found help from this.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply