OPENXML Query Help..... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

OPENXML Query Help.....

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2011-08-31 : 15:35:15

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'. Becuase 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
FROM			XmlDataTable D1
OUTER APPLY		D1.ResultXML.nodes('./SinglePVar') nodeslist(nodeEntry)
LEFT OUTER JOIN tblData1 D2
ON				D1.PartyName = D2.PartyName
WHERE			D1.RunId = 542016
AND				D1.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 !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-31 : 15:48:01

You can filter the wanted elements already in your OUTER APPLY directive.

N 56°04'39.26"
E 12°55'05.63"

sqldba20
Posting Yak Master

183 Posts

Posted - 2011-08-31 : 15:53:14

Could you provide me with an example on how to do this?

quote:
Originally posted by SwePeso

You can filter the wanted elements already in your OUTER APPLY directive.

N 56°04'39.26"
E 12°55'05.63"

sqldba20
Posting Yak Master

183 Posts

Posted - 2011-09-01 : 10:21:41

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources