0

I have a table with 7 columns:

SELECT [Policy_ID], [DateAdded], [DateUpdated], [PartyRecords], [AccountNumber], [UpdatedBy], [UpdatedOn] FROM [Policy_Stat]

The PartyRecords column stores XML that looks like this:

enter image description here

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.

enter image description here

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.

Improve this question
1
  • Syntax will vary by RDBMS; what database platform? Looks like SQL server with the [] around the column names... Also what is the data type for "PartyRecords"? Looks to be XML base don your select; but want to confirm... As far as IDT; why couldn't you just create a derived table where you define the SQL? (right click in white space of Data Foundation layer, Insert Derived table, paste in SQL) Just think of it as a View; you already have the SQL you need; the derived table is just that view; or you could create it as a view in the DB, and pull it into your Data Foundation Layer Commented Apr 22 at 15:17

1 Answer 1

Reset to default
0

I'd suggest either saving the query as a view in the database which you can then just pull in normally; or use a derived table and put the SQL in a derived table.

Right click open space on Data foundation layer. From Popup-->Insert--> Derived Table.

enter image description here

The SQL is executed against the engine; so... as Long as the SQL will execute in SSMS or some other query tool; it should execute here provided correct drivers are in use.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.