locked
BLOB/XML Parser? RRS feed

  • Question

  • I need to query an XML batch report from a SQL Server 2008 table. I need to select particular elements in the XML and store them as variables in another SQL table. I intitialy thought I would be querying a column with xml data type, which was finally starting to look manageable, but after receiving a sample of the database it turns out that the XML report is actually stored as BLOB Data in the SQL table, as "image" data type.

    Does anyone have experience parsing BLOB into XML? Can it be done?

    I have been given the XML Schema. Can BLOB Data reference a Schema?

     

     

     

    Friday, July 2, 2010 1:50 PM

Answers

  • I found a solution to this on another forum if anyone is interested. To parse the BLOB into XML, you would code similar to this:

    CREATE TABLE	#Sample
    		(
    			BLOB VARBINARY(MAX) NOT NULL
    		)
    
    INSERT	#Sample
    SELECT	0x3C526F772049443D2231223E5065736F2C204D56503C2F526F773E
    
    SELECT	*
    FROM	#Sample
    
    -- Workaround
    ALTER TABLE	#Sample
    ADD		MyXML AS CAST(CAST(BLOB AS VARCHAR(MAX)) AS XML)
    
    -- Display the result
    SELECT	*
    FROM	#Sample
    

    The resuting XML can then reference the Schema

     

    • Marked as answer by cpender Monday, July 5, 2010 1:08 PM
    Monday, July 5, 2010 11:16 AM