To convert varbinary(max) to xml format

Question

0x1F8B0800000000000400EDBD07601C499625262F6DCA7B7F4AF54AD7E074A10880601324D8904010ECC188CDE692EC1D69472329AB2A81CA6556655D661640CCED9DBCF7DE7BEFBDF7DE7BEFBDF7BA3B9D4E27F7DFFF3F5C6664016CF6CE4ADAC99E2180AAC81F3F7E7C1F3F221E7F59CFF23A7DB72897CD671FCDDB76F5E8EEDD26CB9AF1A41D4FABC5DDCBFB1FC9B78F0AFBFDD5D5D5F8EADEB8AA2FEEEEEDECECDEFDBDBF78FE7A3ACF17D976B16CDA6C39CD3F3A7A5C01F0B7F38CFE3D7A9C4DDBA25A1E2DAA59717E7DB26EDA6A91D78FEFEAC78FE9A576DD1C35EBE9346F9AC777F56F81F17BE5D747E7D387FB9F660FF2ED6CE7DE83EDFDC9EEDEF6E4E0D3E9F6C1BDFD07D964B27F7EB0BFF7F8AE6D2E2F9ECD8EB20793D9C383F3DDEDDD7BF7EF6FEF9F4F0EB60F26BB93ED1D3CBB7B3BD39DEC40DFA3D68F9BBCBE2CA6F9CBBABA2CE4A35D42A6F7E1E3FCFC3C27D42FF3A7599BBF2916F911D1E1E1F6CEC1F6BDDD373B078FF6761FEDEE8DEFDFDFDBB9FFE9C1273BBB8F76761EDFEDBF2468BE0F8CF085C753A5A42570B36E56F972D6A3ECAC5AE68EACE635906AF7DEDEDEEECEFDC777FD0F6D8BB3D95DF7C76B7E3D2D1E2D8BF2B38FDA7A9D7F846FAB654BBD35FCEB62952DAF5F648B9CFE6AEB6C562C2FF4AF495196F4D7F1745AAD97DC3A6BDBBA98ACDB1C7FD8EE8F1EAF89E08DFC387A4CAC77362394EEDA21CE72FC724934E88D7259B54FC3819645BE6C05EF237E8DBE0C3ED4263CCEF67A951F1D3FFDE2ECC5E3BBFCFB639AEF655BB4D72775CEBF656513FB3040D37CED7E3B2E8BAC39BA7E7C37FC40BF3F2F689CD52AAF338CA5A9AB6AF17B4E5AA2EA92B8052268DEE386E6A5BC065D57D9343F7AF2E6E4CB2F6C23EF0BD7210DD343D6FD7ED7FFE30D0DF92E64803E6A7F629D37C0C77E70BC6CAEF29A67CA03B5CCAF7C327C20E40E348BFE0D1F361D5E528EFCBD17E5ABFCFCAE20E998BBCBBF7785D17E8ED8EDE9E9B3E3AF9EBFF9A133DCBD7BF9F2A2CE2FDABC9AFD88DD7EC46E3F1476FB11BFFD9CF1DB5DB5AA06FCCB329B923966EFCF9FA9EEEB3DB2F7E0C721CEAB72F6A67A93D78BA3731A0231A6F7C9E3699E35F92BFAA75AF6E0057E23885CB427593D3B5EB7F3AA2E1AB192D4705557B3F5B4E5FECEDA7C81CFD4553B66C171DF0C7DE1F8FA887C2BFBFBE3B9F61EBCA5CEAB3A30D62DD42F1FD7F92FC2CC076DCDB4BC365E59D828EE9C29DC936A961F7DFBCBD76FBE7C79766C7BE34F4D13F244F7EEDBAF3CFFF54CBD70FAE8C1EEDEA7F7EF3FD8DB7EF860BAB3FDE9F9DEDE76BEB3BF4F0EF0EEFEFEF9A7D96CE7D37D07C2BDD70515E12C6D71BA9CBDAC8A65FB555D1E697CB0BB333E78307EB03FDEDBD97BF4E9EEEEEE83BB4FDE8CCF5EFFE4F12C5BB5793DFEEEB75F874EF5DDDE27E3E6726A11F3FB20FE274552321FB062BB6BA6AB682E5FE5CD8AD823C77CC86F4C31A00CEA7B1F3DCEEBBAAAF1EB5DFDFD69DE4CEB62A5C27B3780E604D0FBDD2AED254FA051D74B929423E74093DCE383C79759B9CE9DBB2D7FD25B1EB4F7012C3C1402B7DE7F04B8FBDDF1D8ABAAECFC4574A37FCDA45B15830FA1E58C776CFFE66F8EDF973867AF8F4FDE9CFDE4F19BD3A7E1009E1D3F7F7DFAF569F3FCECE4F4C5EBD337BFCFCBD310EED9EB9710A5DD873B0FBF3EF4A75F7E717CF6E2ECC5B32F5F1EBF7EFDDD2F5F85C8DFFDDA205F1C7F11E2FB7540F1D84F4E7FFFFEE067F979B62EDBAF3FF02F5F7D7EFCE2ECA78EDF9C7DF9E2AC33631FCCCF67AF7FFFE3AFDE101DFACC207623C6CB5DAE337A4258D8FFCBFBB2A7FC6F340ABBFFAF350AA74F7FFFD3DFFBE4DBC72F3E3F1DB60DF7DEC336ECFEBFC6367CFBB46B1B7A9FFCC836FCFFD7367CF5FAF4D5F3A7C72F5F1EBFF97638027C4A0C74F2E2B34DD992D1975F7DA684C4AF143DC877F8635E352DB9A8A3A7279F7DFB14FF96D5342BBF3EBD07EDD89B575FFD6C98B1E7676F3E002CB992AF8E3FEF80BCBFF3F501D2F065CABFE1A1FFEC58B197CF8FFB96F6A82CDA3CFD1022FCBC358E7B3F328E3F328E3F328E56B0361B479B78FB2198C77BC3C9B61F59C79F5BEBF84D04B83F328FFF2F308F035FE015C980FE3F77D83928591F0000

Can someone guide me to convert the above varbinary(max) to xml format (decrypt) in sql server 2005?

When I tried cast data as xml I get this error:

XML parsing: line 0, character 0, unrecognized input signature

It's not XML, its a blob of binary data. 0x1f8b08 is a gzip signature, *is* it gzipped data? if so your better off pulling the data back and using a gzip library in a client language to decompress it — Alex K., Jun 28 '11 at 17:21
@Conrad Frix I saw the grouped nulls 000000 @ the beginning so knew it was binary and probably started with a header so I just googled for the 1st 3 bytes — Alex K., Jun 28 '11 at 17:47

score 10 · Answer 1 · answered Dec 21 '11 at 09:49

10

I know it's been a while since you asked this but I think this is what you're looking for:

 CONVERT(xml,(CONVERT(varbinary(max),ColumnName)))

I'm using this with good results

answered Dec 21 '11 at 09:49

Gisli

734
2
11
34

1

select CONVERT(xml,(CONVERT(varbinary(max),))) from where – Malik Aqib Dec 26 '16 at 06:48

score 1 · Answer 2 · answered Nov 20 '18 at 10:00

1

In SQL Server 2016, We have an option of COMPRESS(...) and DECOMPRESS(...) function to convert XML to varbinary(max) and vice-versa.

answered Nov 20 '18 at 10:00

DIBYA RANJAN TRIPATHY

264
3
5

Conrad Frix · Answer 3 · 2011-06-28T17:53:21.270

Alex is correct. If you write out the binary to file and unzip it it contains a file which is an XML file.

Here's the beginning of it

<Order xmlns="http://saas.bt.com/v5" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <orderHeader>
  <action>modifyCustomer</action> 
  <status>success</status> 
  <orderKey>fc946a7e-a037-4b12-b86c-8347abb4f842</orderKey> 
  <orderId>a7bd98f1-1355-4fb8-8b1b-00000120c0a8</orderId> 
  <serviceProviderId>1</serviceProviderId> 
  <effectiveDateTime>2009-08-31T08:21:12.5520568+01:00</effectiveDateTime> 
  <orderDateTime>2009-08-31T08:21:12.5520568+01:00</orderDateTime> 
 <customer>
  <action>suspend</action> 
  <status>done</status> 
  <customerKey>1322105</customerKey> 
  <customerId /> 
  <customerStatus i:nil="true" /> 
  <contacts /> 
  <companyName /> 
  <tradingName /> 
  <billingAccounts /> 
  <attributes /> 
  </customer>
 <users>
 <user>

What you do with this depends on your needs. For example if you just need this one file you can copy it into the Hex Editor of your choice and save it. Then use a gzip client to decompress it.

If on the other hand you need to migrate the data it will depend on your capabilities and the requirements

To convert varbinary(max) to xml format

3 Answers3