November 25, 2013 at 6:16 pm
Hi all,
I am at a loss on how to structure a for xml query that would look like this. I've been at it for a couple of hours (I'm ashamed to admit), and I can't get it right. Each asset has the same ENTITY_ID of 412368, but it is uniquely identified using the MATURITY_ID which is also the identifier. One Maturity_ID per asset/identifier. test table with data is below.
Thank you in advance,
Sharon
<ASSET>
<IDENTIFIERS>
<IDENTIFIER value="391919665" />
</IDENTIFIERS>
<RATINGS>
<RATING type="1232666" val="1" />
<RATING type="1232666" val="2" />
</RATINGS>
</ASSET>
<ASSET>
<IDENTIFIERS>
<IDENTIFIER value="391919673" />
</IDENTIFIERS>
<RATINGS>
<RATING type="1232668" val="3" />
<RATING type="1232668" val="4" />
</RATINGS>
</ASSET>
<ASSET>
<IDENTIFIERS>
<IDENTIFIER value="393225798" />
</IDENTIFIERS>
<RATINGS>
<RATING type="1234239" val="5" />
<RATING type="1234239" val="6" />
</RATINGS>
</ASSET>
create table #ElementContents
(ENTITY_ID INT, INSTRUMENT_ID int,MATURITY_ID int, val varchar(10))
insert into #ElementContents (ENTITY_ID, INSTRUMENT_ID, MATURITY_ID, val)
values ('412368','1232666','391919665',1)
insert into #ElementContents (ENTITY_ID, INSTRUMENT_ID, MATURITY_ID, val)
values ('412368','1232666','391919665',2)
insert into #ElementContents (ENTITY_ID, INSTRUMENT_ID, MATURITY_ID, val)
values ('412368','1232668','391919673',3)
insert into #ElementContents (ENTITY_ID, INSTRUMENT_ID, MATURITY_ID, val)
values ('412368','1232668','391919673',4)
insert into #ElementContents (ENTITY_ID, INSTRUMENT_ID, MATURITY_ID, val)
values ('412368','1234239','393225798',5)
insert into #ElementContents (ENTITY_ID, INSTRUMENT_ID, MATURITY_ID, val)
values ('412368','1234239','393225798',6)
November 26, 2013 at 9:43 am
Try:
SELECT
MATURITY_ID AS [IDENTIFIERS/IDENTIFIER],
(
SELECT
INSTRUMENT_ID AS [@type],
val AS [@val]
FROM
#ElementContents AS E
WHERE
E.MATURITY_ID = T.MATURITY_ID
FOR XML PATH('RATING'), TYPE
) AS [RATINGS]
FROM
(SELECT DISTINCT MATURITY_ID FROM #ElementContents) AS T
FOR XML PATH('ASSET');
GO
November 26, 2013 at 10:28 pm
Hi hunchback,
That did the trick, thank you!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply