August 31, 2011 at 2:34 pm
Folks:
I need help with writing a OPENXML query. I have the below mentioned query which shreds the XML data from column 'ResultXML' from table 'XmlDataTable'. Because of the size of the XML data this query takes a long time. I read that using OPENXML it would be faster and wanted any help on writing the OPENXML query.
SELECT
nodeEntry.value('(./Params/PName)[1]','VARCHAR(250)') AS PName,
nodeEntry.value('(./Params/PGrpId)[1]','INT') AS PGrpId,
nodeEntry.value('(./Params/AsOf)[1]','DATETIME') AS AsOf,
nodeEntry.value('(./SummStat/TrackError)[1]','FLOAT') AS TrackError,
nodeEntry.value('(./SummStat/S_V_95)[1]','FLOAT') AS V_95,
nodeEntry.value('(./SummStat/S_V_99)[1]','FLOAT') AS V_99,
nodeEntry.value('(./SummStat/PValue)[1]','FLOAT') AS Value,
D2.TracingDetails,
D2.SysDate,
D2.Name
FROMXmlDataTable D1
OUTER APPLYD1.ResultXML.nodes('./SinglePVar') nodeslist(nodeEntry)
LEFT OUTER JOIN tblData1 D2
OND1.PartyName = D2.PartyName
WHERED1.RunId = 542016
ANDD1.SysDate = '08/28/2011'
Here is the XML data in ResultXML column:
<SinglePVar>
<Params>
<PName>BIS_Model</PName>
<PGrpId>224</PGrpId>
<AsOf>2003-12-31T00:00:00</AsOf>
<MName>Cash</MName>
<StdevPList>Window</StdevPList>
</Params>
<SummStat>
<UnTrackError>1.194670829272935e-001</UnTrackError>
<TrackError>4.138461149242335e-001</TrackError>
<Un_V_95>1.965233514153978e-001</Un_V_95>
<Un_V_99>3.077472056207081e-001</Un_V_99>
<S_V_95>6.807768590503640e-001</S_V_95>
<S_V_99>1.066067592044826e+000</S_V_99>
<SFact>1.200000000000000e+001</SFact>
<PValue>4.098631071641542e+008</PValue>
<ConMBAgent>6.541920628018216e-001</ConMBAgent>
</SummStat>
<FContribTV>
<FContribTVEntry>
<Fact>Agency</Fact>
<VContrib>-6.551961080087127e-003</VContrib>
<Multiplier>5.000000000000000e-001</Multiplier>
<MVTE>3.243186331733051e+001</MVTE>
</FContribTVEntry>
</FContribTV>
</SinglePVar>
Thanks
August 31, 2011 at 3:36 pm
Change your query to
SELECT
nodeParams.value('PName[1]','VARCHAR(250)') AS PName,
nodeParams.value('PGrpId[1]','INT') AS PGrpId,
nodeParams.value('AsOf[1]','DATETIME') AS AsOf,
nodeSummStat.value('TrackError[1]','FLOAT') AS TrackError,
nodeSummStat.value('S_V_95[1]','FLOAT') AS V_95,
nodeSummStat.value('S_V_99[1]','FLOAT') AS V_99,
nodeSummStat.value('PValue[1]','FLOAT') AS Value,
D2.TracingDetails,
D2.SysDate,
D2.Name
FROM XmlDataTable D1
OUTER APPLY D1.ResultXML.nodes('SinglePVar') nodeslist(nodeEntry)
OUTER APPLY nodeEntry.nodes('Params') nodeslist2(nodeParams)
OUTER APPLY nodeEntry.nodes('SummStat') nodeslist3(nodeSummStat)
LEFT OUTER JOIN tblData1 D2
ON D1.PartyName = D2.PartyName
WHERE D1.RunId = 542016
AND D1.SysDate = '08/28/2011'
If performance is still not sufficient, add an xml index to the ResultXML column and / or shred the data into an indexed temp table and join that temp table instead of the xml column.
September 1, 2011 at 8:16 am
Tried with the new query but performance is a issue. If anybody can help with OPENXML query that would be great. I know in the past some has said that with OPENXML it takes lots of memory but I'm fine with it becuase once all the data from XML is shredded that is a it.
I have written this OPENXML query and it works to shred the XML data but I don't know how to get the data from the other columns in the 'select' list which has joins (D2.TracingDetails,D2.SysDate,D2.Name) using this query.
DECLARE @hdoc int
DECLARE @inputXML XML
SET @inputXML = (SELECT CAST(ResultXML AS VARCHAR(MAX)) from tblXmlData where RunId = 526014)
exec sp_xml_preparedocument
@hdoc OUTPUT,
@inputXml
SELECT PName,
PGrpId,
AsOf,
TrackError,
S_V_95,
S_V_99,
PValue
FROM OPENXML(@hdoc, './SinglePVar/Params',2)
WITH (PName VARCHAR(100),
PGrpId INT,
AsOf DATETIME)
CROSS APPLY OPENXML(@hdoc,'./SinglePVar/SummStat',2)
WITH (TrackError FLOAT,
S_V_95 FLOAT,
S_V_99 FLOAT,
PValue FLOAT)
exec sp_xml_removedocument @hdoc
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply