• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1130
  • Last Modified:

Extract Binary XML data to readable XML file

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
ImageryGrl
Asked:
ImageryGrl
  • 3
  • 3
1 Solution
 
BrandonGalderisiCommented:
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
 
ImageryGrlAuthor Commented:
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
 
BrandonGalderisiCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
ImageryGrlAuthor Commented:
All it gives me is a single row with a NULL.  
0
 
BrandonGalderisiCommented:
try removing one or both of your criteria.  That date doesn't seem valid to me.
0
 
ImageryGrlAuthor Commented:
Thanks.  I got it based on your information.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now