Parse a text Data Type that contains XML data in SQL 2005

[lexi0088]

I am trying to figure out how to create a query (or view) that will create several columns for this one column of data. The data type for this column is text (I did not set this up & I cannot change it). The column [records_XML_String] from table [WebApps].[dbo].[cm_app_xmlform_records] is listed below

<uservalues> <field name="speaker_Name_First">JOE</field> <field name="speaker_Name_Last">BLOW</field> <field name="speaker_Description">Lunch</field> <field name="speaker_EventLocation">8320 AVE WAY INDIANAPOLIS,INDIANA</field> <field name="speaker_Date">10/04/10</field> <field name="speaker_PresentationType">Teleconference</field> <field name="speaker_Product">Deplin</field> <field name="speaker_Fee">$1500</field> <field name="speaker_Booth">No</field> <field name="speaker_Manager">redwards@company.com</field> <field name="speaker_SendInvite"></field> <field name="speaker_Comments">SPEAKING TO 20 PSYCHIATRIST AND STAFF ABOUT DEPLIN DURING A TELECONFERENCE.</field> </uservalues>


ANY help would be appreciated...

 

[gmmastros]

I'm not too familiar with querying XML data, but I was playing around a little and came up with something that may help you get started.

Create Table #Temp (Data Text)

Insert Into #Temp Values('
<uservalues> 
	<field name="speaker_Name_First">JOE</field> 
	<field name="speaker_Name_Last">BLOW</field> 
	<field name="speaker_Description">Lunch</field> 
	<field name="speaker_EventLocation">8320 AVE WAY INDIANAPOLIS,INDIANA</field> 
	<field name="speaker_Date">10/04/10</field> 
	<field name="speaker_PresentationType">Teleconference</field> 
	<field name="speaker_Product">Deplin</field> 
	<field name="speaker_Fee">$1500</field> <field name="speaker_Booth">No</field> <field name="speaker_Manager">redwards@company.com</field>  <field name="speaker_SendInvite"></field> <field name="speaker_Comments">SPEAKING TO 20 PSYCHIATRIST AND STAFF ABOUT DEPLIN DURING A TELECONFERENCE.</field> </uservalues> ')

Select	*, 
		XMLData.value('(//uservalues/field[@name="speaker_Name_First"])[1]','varchar(50)'),
		XMLData.value('(//uservalues/field[@name="speaker_Name_Last"])[1]','varchar(50)'),
		XMLData.value('(//uservalues/field[@name="speaker_Description"])[1]','varchar(50)'),
		XMLData.value('(//uservalues/field[@name="speaker_EventLocation"])[1]','varchar(50)'),
		XMLData.value('(//uservalues/field[@name="speaker_Date"])[1]','DateTime')

From	(
		Select Convert(xml, Data) As XMLData
		From #Temp
		) As A

Drop Table #Temp

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lexi0088]

That Worked Great!! Thank you for your help. But I still have one problem, it kicks out data that has an & in it. So how would I escape these special characters?

 

[gmmastros]

Well.... it gets ugly.

You stated earlier that you cannot change the table structure. "(I did not set this up & I cannot change it)". I can understand not wanting to change the data type to XML, but if you can convince someone to change it to varchar(max), then things would be a bit simpler.

Anyway... to fix this problem, we need to replace & with &amp; You cannot use the replace function on a text column, so you need to first convert it to varchar(max) and then do the replace, like this:

Create Table #Temp (Data Text)

Insert Into #Temp Values('
<uservalues>
    <field name="speaker_Name_First">JOE</field>
    <field name="speaker_Name_Last">BLOW</field>
    <field name="speaker_Description">Lunch</field>
    <field name="speaker_EventLocation">8320 AVE WAY [!]&[/!] INDIANAPOLIS,INDIANA</field>
    <field name="speaker_Date">10/04/10</field>
    <field name="speaker_PresentationType">Teleconference</field>
    <field name="speaker_Product">Deplin</field>
    <field name="speaker_Fee">$1500</field> <field name="speaker_Booth">No</field> <field name="speaker_Manager">redwards@company.com</field>  <field name="speaker_SendInvite"></field> <field name="speaker_Comments">SPEAKING TO 20 PSYCHIATRIST AND STAFF ABOUT DEPLIN DURING A TELECONFERENCE.</field> </uservalues> ')

Select    *,
        XMLData.value('(//uservalues/field[@name="speaker_Name_First"])[1]','varchar(50)'),
        XMLData.value('(//uservalues/field[@name="speaker_Name_Last"])[1]','varchar(50)'),
        XMLData.value('(//uservalues/field[@name="speaker_Description"])[1]','varchar(50)'),
        XMLData.value('(//uservalues/field[@name="speaker_EventLocation"])[1]','varchar(50)'),
        XMLData.value('(//uservalues/field[@name="speaker_Date"])[1]','DateTime')

From    (
        Select Convert(xml, [!]Replace(Convert(VarChar(max), Data), '&', '&amp;')[/!]) As XMLData
        From #Temp
        ) As A

Drop Table #Temp

Please not that it's not just ampersands that you will have problems with. It's other characters too. What's worse is.... & is not valid xml, so it should never have been put in to the table that way. If you have well formed XML in the table, and poorly formed XML in the table, you have bigger problems. For example, well formed XML would look like this:

<data>Intersection of 5th &amp; main</data>

If you replace the ampersand in that string, you'll end up with:

<data>Intersection of 5th &amp;amp; main</data>

Meaning... you could take good data and make it bad. I don't envy you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lexi0088]

OMG, you are a God Send! God blease you and thank you so much! You have saved me so much heartache.