December 8, 2009 at 10:10 pm
I am parsing XML that is stored in the following format:
<relatedOSCollection>
<oddsStatement id="001C4AB240FE4AB3A4A281C4734C406C">
<relatedOS id="FD17415A83EC4FE08E21A9922F6BCF1C" relationType="s" />
<relatedOS id="39BE1CBA054149B684934A475976E361" relationType="s" />
<relatedOS id="B13B2519312A4AD78077E04AC3EC6BFD" relationType="s" />
<relatedOS id="65667E0CD31B4AD1B2AAE197A2851F7F" relationType="c" />
<relatedOS id="656C3771AC28442486DD7C97F971A5B3" relationType="c" />
</oddsStatement>
<oddsStatement>
...
</oddsStatement>
</relatedOSCollection>
Running the following SQL code is very fast (2 sec for 28k oddsStatements):
SELECT tab.col.value('./@id','nvarchar(65)') as 'os_uri',
tab.col.value('./relatedOS[1]/@id','varchar(65)') as 'relatedOS_1'
--tab.col.value('./relatedOS[2]/@id','varchar(65)') as 'relatedOS_2'
--tab.col.value('./relatedOS[3]/@id','varchar(65)') as 'relatedOS_3',
--tab.col.value('./relatedOS[4]/@id','varchar(65)') as 'relatedOS_4',
--tab.col.value('./relatedOS[5]/@id','varchar(65)') as 'relatedOS_5'
FROM EXPORTS_XML
CROSS APPLY
export_xml.nodes('//oddsStatement') AS tab(col)
WHERE export_id = 336
But uncommenting the 2nd relatedOS slows the query to nearly inoperable speeds. Ideally, I'd like to grab all relatedOS. I tried using the path '//relatedOS' and then '../@id' for the parent but it was no better. Any ideas would be greatly appreciated!
December 8, 2009 at 10:29 pm
An update.... I tried this code and it worked much better. Still not quite sure why the first was not working well.
SELECT tab.col.value('./@id','nvarchar(65)') as 'os_uri',
tab.col.query('./relatedOS[1]').value('(//@id)[1]','varchar(65)') as 'relatedOS_1',
tab.col.query('./relatedOS[2]').value('(//@id)[1]','varchar(65)') as 'relatedOS_2',
tab.col.query('./relatedOS[3]').value('(//@id)[1]','varchar(65)') as 'relatedOS_3',
tab.col.query('./relatedOS[4]').value('(//@id)[1]','varchar(65)') as 'relatedOS_4',
tab.col.query('./relatedOS[5]').value('(//@id)[1]','varchar(65)') as 'relatedOS_5'
FROM EXPORTS_XML
CROSS APPLY
export_xml.nodes('relatedOSExport/relatedOSCollection/oddsStatement') AS tab(col)
WHERE export_id = @EXPORT_ID
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply