June 6, 2013 at 8:17 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy