[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Extract Binary XML data to readable XML file

Posted on 2008-10-06
6
Medium Priority
?
1,130 Views
Last Modified: 2012-05-05
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
SELECT
      @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' 
a
 
SELECT DISTINCT
      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)

Open in new window

0
Comment
Question by:ImageryGrl
  • 3
  • 3
6 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22651594
Your @xmlBlob should have your XML.  Not sure what the question is.

Are you asking how to search the extracted XML not knowing the structure?
0
 

Author Comment

by:ImageryGrl
ID: 22651629
Yes exactly.  as it is stored as <Binary data>, I cannot just review it.  I was hoping to extract the full content off to a document so I could more easily perform searches on it.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1500 total points
ID: 22651768
Your @xmlBlob will contain the entire document.

Just do a select @XMLBlob:


DECLARE @xmlblob xml
SELECT
      @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'

select @xmlBlob
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 

Author Comment

by:ImageryGrl
ID: 22651915
All it gives me is a single row with a NULL.  
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22654242
try removing one or both of your criteria.  That date doesn't seem valid to me.
0
 

Author Closing Comment

by:ImageryGrl
ID: 31503477
Thanks.  I got it based on your information.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question