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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy