Parsing XML in a non-XML column

Posted by slugster on Stack Overflow See other posts from Stack Overflow or by slugster
Published on 2010-04-12T22:50:16Z Indexed on 2010/04/12 22:52 UTC
Read the original article Hit count: 731

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">
    <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!

© Stack Overflow or respective owner

Related posts about sql-server-2005

Related posts about sql-server-2008