BLOB Data Parser - 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

General SQL Server Forums

New to SQL Server Programming

BLOB Data Parser

Author

Topic

cpender
Starting Member

11 Posts

Posted - 2010-07-05 : 05:29:05

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 "varbinary(MAX)" 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?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-05 : 06:25:25

1) Can BLOB data reference a Schema?
No. A BLOB is only a sequence of binary values

2) Can it be done?
Yes. You can, for example, add a calculated column.

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

N 56°04'39.26"
E 12°55'05.63"

cpender
Starting Member

11 Posts

Posted - 2010-07-05 : 06:49:48

Thanks for that. That seems to have worked quite well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources