on 2025 Jan 15 1:07 AM
I have a table with 7 fields:
SELECT [Policy_ID]
,[DateAdded]
,[DateUpdated]
,[PartyRecords]
,[AccountNumber]
,[UpdatedBy]
,[UpdatedOn]
FROM [Policy_Stat]
The PartyRecords field stores XML that looks like this:
In my universe, using IDT, I need to pull each party record out into it's own set of dimensions - PartyTypeCode, PartyName, PartyCorrespondenceName, LocationAddressLine1, LocationAddressLine2, LocationCity, LocationStateCode, LocationPostalCode, PartyRole.
In the case above, I would end up with 3 sets of dimensions. In sql, I would use cross apply, for example:
SELECT PartyRecord.value('PartyName[1]', 'nvarchar(100)') AS PartyName
FROM Policy_Stat
CROSS APPLY PartyRecords.nodes('/ArrayOfPartyRecord/PartyRecord') AS X(PartyRecord)
However, I'm not sure how to approach this in IDT. Any suggestions would be welcome.
Request clarification before answering.
| User | Count |
|---|---|
| 7 | |
| 7 | |
| 6 | |
| 5 | |
| 5 | |
| 3 | |
| 3 | |
| 3 | |
| 3 | |
| 2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.