cancel
Showing results for 
Search instead for 
Did you mean: 
Read only

Parsing out XML field to create new dimensions

Hlock
Discoverer
143

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:

PartyXML.png

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. 

PartyDimensions.png

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.

Accepted Solutions (0)

Answers (0)