OpenXML Query !

  • Folks:

    I need help with XML query. I have this XML data and I'm using OPENXML to shred the data into table. For each ModelValue (/DataOutputEntries/ARun/SecXml/Sec/SRateArr/SRateArrValues/ModelValue) I'm writing 12 insert statements (in this example I have only 3 insert statements) and each insert takes around like 2 to 3 seconds because of the huge XML data which has 46K lines (attached example has less data). Can this be done in a better way? Will cross apply be faster? I have never used cross apply before so any help on the query will be appreciated.

    Thanks !

    DECLARE @ADataOutputImportEntries XML

    SET @ADataOutputImportEntries=

    '<DataOutputEntries>

    <ARun>

    <JobId>9390678</JobId>

    <JobEntry>1088801</JobEntry>

    <SKey>Tester=1.000/EquationTesting=Zero</SKey>

    <Label>TESTING</Label>

    <SecXml>

    <Sec>

    <ADate>2012-03-30</ADate>

    <SId>10508</SId>

    <Name>320John</Name>

    <Ticker />

    <Cip>328T7</Cip>

    <SDate>2012-01-30</SDate>

    <SRateArr>

    <SRateArrValues>

    <CollType>All</CollType>

    <GroupNumber>0</GroupNumber>

    <GroupName>TOTAL</GroupName>

    <ModelValue>

    <Date>3/1/2012</Date>

    <CurrentToThirtyTo59>0.0442587393797255</CurrentToThirtyTo59>

    <CurrentToVoluntaryPrepay>0.00112852461455603</CurrentToVoluntaryPrepay>

    <ThirtyTo59ToCurrent>0.239157153759315</ThirtyTo59ToCurrent>

    <ThirtyTo59ToSixtyTo89>0.241455128769983</ThirtyTo59ToSixtyTo89>

    <NinetyPlusToInvoluntaryPrepay>0.0179558248028735</NinetyPlusToInvoluntaryPrepay>

    <CurrentToCurrent>0.954612736005718</CurrentToCurrent>

    <ThirtyTo59ToThirtyTo59>0.518541993321672</ThirtyTo59ToThirtyTo59>

    <SixtyTo89ToSixtyTo89>0.340082560007853</SixtyTo89ToSixtyTo89>

    <NinetyPlusToNinetyPlus>0.83600521461896</NinetyPlusToNinetyPlus>

    <SixtyTo89ToCurrent>0.103180895929781</SixtyTo89ToCurrent>

    <NinetyPlusToCurrent>0.0441401509179685</NinetyPlusToCurrent>

    <SixtyTo89ToNinetyPlus>0.298778375525492</SixtyTo89ToNinetyPlus>

    </ModelValue>

    <ModelValue>

    <Date>4/1/2012</Date>

    <CurrentToThirtyTo59>0.0499544306845915</CurrentToThirtyTo59>

    <CurrentToVoluntaryPrepay>0.00109431768188259</CurrentToVoluntaryPrepay>

    <ThirtyTo59ToCurrent>0.199535150055279</ThirtyTo59ToCurrent>

    <ThirtyTo59ToSixtyTo89>0.273414973899818</ThirtyTo59ToSixtyTo89>

    <NinetyPlusToInvoluntaryPrepay>0.0176055701572801</NinetyPlusToInvoluntaryPrepay>

    <CurrentToCurrent>0.948678229151541</CurrentToCurrent>

    <ThirtyTo59ToThirtyTo59>0.525995695441001</ThirtyTo59ToThirtyTo59>

    <SixtyTo89ToSixtyTo89>0.337274044725917</SixtyTo89ToSixtyTo89>

    <NinetyPlusToNinetyPlus>0.836278799696028</NinetyPlusToNinetyPlus>

    <SixtyTo89ToCurrent>0.0814687575120575</SixtyTo89ToCurrent>

    <NinetyPlusToCurrent>0.0441429483508975</NinetyPlusToCurrent>

    <SixtyTo89ToNinetyPlus>0.337497252914493</SixtyTo89ToNinetyPlus>

    </ModelValue>

    </SRateArrValues>

    <SRateArrValues>

    <CollType>FloatingRate</CollType>

    <GroupNumber>6</GroupNumber>

    <GroupName>2A</GroupName>

    <ModelValue>

    <Date>6/1/2012</Date>

    <CurrentToThirtyTo59>0.0611408854165271</CurrentToThirtyTo59>

    <CurrentToVoluntaryPrepay>0.000678007870239213</CurrentToVoluntaryPrepay>

    <ThirtyTo59ToCurrent>0.163265030897998</ThirtyTo59ToCurrent>

    <ThirtyTo59ToSixtyTo89>0.327657418802434</ThirtyTo59ToSixtyTo89>

    <NinetyPlusToInvoluntaryPrepay>0.015565453655582</NinetyPlusToInvoluntaryPrepay>

    <CurrentToCurrent>0.937548253411662</CurrentToCurrent>

    <ThirtyTo59ToThirtyTo59>0.50798882336555</ThirtyTo59ToThirtyTo59>

    <SixtyTo89ToSixtyTo89>0.341381810295919</SixtyTo89ToSixtyTo89>

    <NinetyPlusToNinetyPlus>0.845436300707964</NinetyPlusToNinetyPlus>

    <SixtyTo89ToCurrent>0.0623208735561162</SixtyTo89ToCurrent>

    <NinetyPlusToCurrent>0.0408626114855406</NinetyPlusToCurrent>

    <SixtyTo89ToNinetyPlus>0.387547418575595</SixtyTo89ToNinetyPlus>

    </ModelValue>

    <ModelValue>

    <Date>7/1/2012</Date>

    <CurrentToThirtyTo59>0.0620749359821378</CurrentToThirtyTo59>

    <CurrentToVoluntaryPrepay>0.000688643874234245</CurrentToVoluntaryPrepay>

    <ThirtyTo59ToCurrent>0.163887436671472</ThirtyTo59ToCurrent>

    <ThirtyTo59ToSixtyTo89>0.326071061950598</ThirtyTo59ToSixtyTo89>

    <NinetyPlusToInvoluntaryPrepay>0.0152817269357896</NinetyPlusToInvoluntaryPrepay>

    <CurrentToCurrent>0.936225626828455</CurrentToCurrent>

    <ThirtyTo59ToThirtyTo59>0.508565470087825</ThirtyTo59ToThirtyTo59>

    <SixtyTo89ToSixtyTo89>0.342158892295477</SixtyTo89ToSixtyTo89>

    <NinetyPlusToNinetyPlus>0.845490061979147</NinetyPlusToNinetyPlus>

    <SixtyTo89ToCurrent>0.0622535698764131</SixtyTo89ToCurrent>

    <NinetyPlusToCurrent>0.0408551960013352</NinetyPlusToCurrent>

    <SixtyTo89ToNinetyPlus>0.38723632596392</SixtyTo89ToNinetyPlus>

    </ModelValue>

    </SRateArrValues>

    </SRateArr>

    </Sec>

    </SecXml>

    </ARun>

    </DataOutputEntries>

    '

    CREATE TABLE #tempTblTransRateArr(

    Label VARCHAR(50),

    ADate DATETIME,

    GroupId INT,

    CollType VARCHAR(50),

    SKey VARCHAR(500),

    BeginState VARCHAR(50),

    EndState VARCHAR(50),

    [Date] DATETIME,

    TransRate FLOAT)

    DECLARE @hdoc INT

    EXEC sp_xml_preparedocument @hdoc OUTPUT, @ADataOutputImportEntries

    INSERT INTO #tempTblTransRateArr

    SELECT

    Label,

    ADate,

    GroupId,

    CollType,

    SKey,

    'Current' as BeginState,

    'ThirtyTo59' as EndState,

    [Date],

    TransRate

    FROM OPENXML(@hdoc, '/DataOutputEntries/ARun/SecXml/Sec/SRateArr/SRateArrValues/ModelValue', 2)

    WITH

    (

    Label VARCHAR(50) '../../../../../Label',

    ADate DATETIME '../../../ADate',

    GroupId INT '../GroupNumber',

    CollType VARCHAR(50) '../CollType',

    SKey VARCHAR(500) '../../../../../SKey',

    [Date] DATETIME 'Date',

    TransRate FLOAT 'CurrentToThirtyTo59')

    INSERT INTO #tempTblTransRateArr

    SELECT

    Label,

    ADate,

    GroupId,

    CollType,

    SKey,

    'Current' as BeginState,

    'VoluntaryPrepay' as EndState,

    [Date],

    TransRate

    FROM OPENXML(@hdoc, '/DataOutputEntries/ARun/SecXml/Sec/SRateArr/SRateArrValues/ModelValue', 2)

    WITH

    (

    Label VARCHAR(50) '../../../../../Label',

    ADate DATETIME '../../../ADate',

    GroupId INT '../GroupNumber',

    CollType VARCHAR(50) '../CollType',

    SKey VARCHAR(500) '../../../../../SKey',

    [Date] DATETIME 'Date',

    TransRate FLOAT 'CurrentToVoluntaryPrepay')

    INSERT INTO #tempTblTransRateArr

    SELECT

    Label,

    ADate,

    GroupId,

    CollType,

    SKey,

    'ThirtyTo59' as BeginState,

    'Current' as EndState,

    [Date],

    TransRate

    FROM OPENXML(@hdoc, '/DataOutputEntries/ARun/SecXml/Sec/SRateArr/SRateArrValues/ModelValue', 2)

    WITH

    (

    Label VARCHAR(50) '../../../../../Label',

    ADate DATETIME '../../../ADate',

    GroupId INT '../GroupNumber',

    CollType VARCHAR(50) '../CollType',

    SKey VARCHAR(500) '../../../../../SKey',

    [Date] DATETIME 'Date',

    TransRate FLOAT 'ThirtyTo59ToCurrent')

    EXEC sp_xml_removedocument @hdoc

    SELECT * FROM #tempTblTransRateArr

    DROP TABLE #tempTblTransRateArr

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply