Cast to XML then query - 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)

Cast to XML then query

Author

Topic

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2012-03-06 : 15:09:23

Hi all,

I have a text field that I wanted to convert to XML and then be able to query certain portions of. I tried 2 ways, one successful and one that locks and never finishes. I am unsure what the differences are, could use some help as I am a begginer to querying XML. Any hints would be appreciated!

XML format aafter convert:


<CLAIMCHECK xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <CLAIM_ROW>
   <More Items></More Items>
  </CLAIM_ROW>
  <DIAG_GROUP>
   <More Items></More Items>
  </DIAG_GROUP>
  <LINE_GROUP>
   <More Items></More Items>
  </LINE_GROUP>
</CLAIMCHECK>

Works:

Select
top 1
Cast(xmldata as xml) as xmldata
Into #xml
From Plandata_rpt.dbo.claimextxml

Select
xmldata.value('(/CLAIMCHECK/LINE_GROUP/LINE_ROW/PROCEDURE_CODE)[1]','nvarchar(max)')
From #xml

Does NOT work:

Select
xmldata.value('(/CLAIMCHECK/LINE_GROUP/LINE_ROW/PROCEDURE_CODE)[1]','nvarchar(max)')
From
(
Select top 1
Cast(xmldata as xml) as xmldata
From Plandata_rpt.dbo.claimextxml

) a

Thanks for any help!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-06 : 20:13:59

I don't see anything suspicious here that would make the second query hang.

The only thought that comes to mind is that because you are not specifying an ordering scheme, TOP 1 may not be returning the same row in the two cases, and if the XML in the second case happens to be very very large, shredding that could be causing the delay. That is a far-fetched and unlikely scenario, but nothing else comes to mind.

Is the entire query what you have shown here, or is there more to it (such as joining with other tables etc.)?

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2012-03-07 : 09:02:00

Thanks,

I eventually got the information that I wanted out of the field. Unfortunately I had to revert to temp tables, although I could of tried CTE as well. And yes the XML is quite large, but it was weird to me that the top 1 statement would return in seconds on its own (the tables is indexed) but would hang the query when put as a derived table.

Thanks again for the help.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-03-08 : 16:46:51

performance hit because of derived table does not have necessary index

1. do you have delimiters in that text field?
2. how does your query convert a text field to xml without a delimiter?
3. why in the world do you have a delimiter in a text field?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources