Parsing XML in a non-XML column

Question

Hi, i am reasonably proficient with SQLServer, but i'm not a DBA so i'm not sure how to approach this.

I have an XML chunk stored in an ntext column. Due to it being a legacy database and the requirements of the project i cannot change the table (yet). This is an example of the data i need to manipulate:

<XmlSerializableHashtable xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
    <Entries>
        <Entry>
            <key xsi:type="xsd:string">CurrentYear</key><value xsi:type="xsd:string">2010</value>
        </Entry>
        <Entry>
            <key xsi:type="xsd:string">CurrentMonth</key><value xsi:type="xsd:string">4</value>
        </Entry>
    </Entries>
</XmlSerializableHashtable>

each row will have a chunk like this, but obviously with different keys/values in the XML. Is there any clever way i can parse this XML in to a name/value pairs style view? Or should i be using SQLServer's XML querying abilities even though it isn't an XML column? If so, how would i query a specific value out of that column?

(Note: adding a computed XML column on the end of the table is a possibility, if that helps).

Thanks for any assistance!

Answer 1

If you can get the column to an XML typed column you can use xpath queries to get data out of it. OTOH I'd guess that a computed column that casts it into XML might work. YMMV, but it shouldn't be that hard to test it.

You can use cross apply to get a repeating group out of an XML valued field. cross apply effectively pokes the value of a column into a table valued function and allows you to join against the results. This link shows an example of how to do this.

I have actually had occasion to do this quite recently (for the first time), but I don't have a code sample to hand and I can't remember it off the top of my head. You can use this technique to self join multiple layers in a hierarchical structure within an XML document.