July 21, 2016 at 4:56 am
Hello
I am looking to perform all of this through SSIS
I have a set of tables from which I create a new table (S_ACT_RATING_NOTES_XML_ONLY)
I then use this table and create a final table
So, at the moment, this involves the creation of 2 tables
I want to bypass the creation of a staging table, by creating a single SSIS dataflow
I'm comfortable taking the initial data set and creating a staging table S_ACT_RATING_NOTES_XML_ONLY (although, again this table creation should not be needed so I wouldn't use a destination at this point)
Structure:
CREATE TABLE [dbo].[S_ACT_RATING_NOTES_XML_ONLY](
[POLICY_KEY] [numeric](10, 0) NOT NULL,
[RATING_NOTE_KEY] [numeric](10, 0) NOT NULL,
[POLICY_PART_KEY] [numeric](10, 0) NOT NULL,
[PRODUCT_SECTION_KEY] [numeric](10, 0) NOT NULL,
[XML_QUERY] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
How do I split up XML_QUERY?
The following works when using a table:
SELECT
POLICY_KEY,
RATING_NOTE_KEY,
POLICY_PART_KEY,
PRODUCT_SECTION_KEY,
xml_query.value('/QuoteBreakdown[1]/SectionName[1]/@Val','varchar(50)') AS SectionName
, xml_query.value('/QuoteBreakdown[1]/ObjectRef[1]/@Val','varchar(50)') AS ObjectRef
, xml_query.value('/QuoteBreakdown[1]/OrigPrem[1]/@Val','varchar(50)') AS OrigPrem
, xml_query.value('/QuoteBreakdown[1]/BrokCommAmt[1]/@Val','varchar(50)') AS BrokCommAmt
, xml_query.value('/QuoteBreakdown[1]/BrokCommRate[1]/@Val','varchar(50)') AS BrokCommRate
, xml_query.value('/QuoteBreakdown[1]/UKGCommAmt[1]/@Val','varchar(50)') AS UKGCommAmt
, xml_query.value('/QuoteBreakdown[1]/UKGCommRate[1]/@Val','varchar(50)') AS UKGCommRate
, xml_query.value('/QuoteBreakdown[1]/InsurerAmt[1]/@Val','varchar(50)') AS InsurerAmt
, xml_query.value('/QuoteBreakdown[1]/CoverCode[1]/@Val','varchar(50)') AS CoverCode
, xml_query.value('/QuoteBreakdown[1]/DiscOrigPrem[1]/@Val','varchar(50)') AS DiscOrigPrem
, xml_query.value('/QuoteBreakdown[1]/DiscBrokCommAmt[1]/@Val','varchar(50)') AS DiscBrokCommAmt
, xml_query.value('/QuoteBreakdown[1]/DiscBrokCommRate[1]/@Val','varchar(50)') AS DiscBrokCommRate
, xml_query.value('/QuoteBreakdown[1]/DiscUKGCommAmt[1]/@Val','varchar(50)') AS DiscUKGCommAmt
, xml_query.value('/QuoteBreakdown[1]/DiscUKGCommRate[1]/@Val','varchar(50)') AS DiscUKGCommRate
, xml_query.value('/QuoteBreakdown[1]/DiscInsurerAmt[1]/@Val','varchar(50)') AS DiscInsurerAmt
FROM dbo.S_ACT_RATING_NOTES_XML_ONLY
What derived column expression works?
I've tried a few with no success e.g.
(DT_STR,50,1252)(xml_query.value('/QuoteBreakdown[1]/DiscInsurerAmt[1]/@Val','varchar(50)'))
For info, example XML_QUERY:
<QuoteBreakdown>
<SectionName Val="Public Liability" />
<CoverCode Val="B205 L20" />
<OrigPrem Val="116.8" />
<DiscOrigPrem Val="116.8" />
<BrokCommAmt Val="26.28" />
<DiscBrokCommAmt Val="26.28" />
<BrokCommRate Val="22.5" />
<DiscBrokCommRate Val="22.5" />
<UKGCommAmt Val="8.76" />
<DiscUKGCommAmt Val="8.76" />
<UKGCommRate Val="7.5" />
<DiscUKGCommRate Val="7.5" />
<InsurerAmt Val="81.76" />
<DiscInsurerAmt Val="82" />
</QuoteBreakdown>
Thanks
Damian.
- Damian
July 21, 2016 at 5:33 am
Quick question out of curiosity, why would you move this into the SSIS dataflow when it will most likely be both simpler and faster when done at the SQL Server source?
😎
Bear in mind that unless the SSIS is executed on a different server, the memory consumption of the SSIS can easily pressure the SQL Server instance, modifying large XML sets within SSIS would be a perfect example thereof.
July 21, 2016 at 6:13 am
Thanks Eirikur
I basically get 3 tables that are fed from our source system and have no control over these
all_rating_notes contains an attribute (full_description) that is actually varchar(max) when it's written to our staging database
I control manipulation from here i.e. create the production tables via SSIS
Are you suggesting another approach?
I suppose (providing a problem rather than a solution), I am trying to take some data and pivot that data out such that it becomes a number of attributes within a table
Part of that data happens to be an XML column
Thanks
- Damian
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply