I am working in PerformancePoint, and the application stores important data in a binary Blob field in a SQL 2005 table. If I know the specific field name that I am searching for I have been able to extract data from this field as in the code snippet. However, now I need to search the entire field for data, but I do not know where it might be stored.
I would like to convert the entire field into an XML document. Is this possible without knowing all of the tags used in the field?
DECLARE @xmlblob xml
@xmlblob = CAST(CAST ( ba.xmlblob AS varbinary(MAX)) as xml)
FROM BizAppNodes ba
WHERE VersionEndDateTime = '9999-12-31 00:00:00.000'
AND BizAppNodeLabel = 'Application'
tab.col.value('../../@Label', 'varchar(30)') as ModelLabel,
tab.col.value('@Server', 'varchar(30)') as CurrentPeriodId
FROM @xmlblob.nodes ('/BizModelSite/Models/ArrayOfBizModel/BizModel/EffectiveDatedCurrentPeriods/EffectiveDatedCurrentPeriod') as tab(col)