将varbinary（max）转换为xml格式

0x1F8B0800000000000400EDBD07601C499625262F6DCA7B7F4AF54AD7E074A10880601324D8904010ECC188CDE692EC1D69472329AB2A81CA6556655D661640CCED9DBCF7DE7BEFBDF7DE7BEFBDF7BA3B9D4E27F7DFFF3F5C6664016CF6CE4ADAC99E2180AAC81F3F7E7C1F3F221E7F59CFF23A7DB72897CD671FCDDB76F5E8EEDD26CB9AF1A41D4FABC5DDCBFB1FC9B78F0AFBFDD5D5D5F8EADEB8AA2FEEEEEDECECDEFDBDBF78FE7A3ACF17D976B16CDA6C39CD3F3A7A5C01F0B7F38CFE3D7A9C4DDBA25A1E2DAA59717E7DB26EDA6A91D78FEFEAC78FE9A576DD1C35EBE9346F9AC777F56F81F17BE5D747E7D387FB9F660FF2ED6CE7DE83EDFDC9EEDEF6E4E0D3E9F6C1BDFD07D964B27F7EB0BFF7F8AE6D2E2F9ECD8EB20793D9C383F3DDEDDD7BF7EF6FEF9F4F0EB60F26BB93ED1D3CBB7B3BD39DEC40DFA3D68F9BBCBE2CA6F9CBBABA2CE4A35D42A6F7E1E3FCFC3C27D42FF3A7599BBF2916F911D1E1E1F6CEC1F6BDDD373B078FF6761FEDEE8DEFDFDFDBB9FFE9C1273BBB8F76761EDFEDBF2468BE0F8CF085C753A5A42570B36E56F972D6A3ECAC5AE68EACE635906AF7DEDEDEEECEFDC777FD0F6D8BB3D95DF7C76B7E3D2D1E2D8BF2B38FDA7A9D7F846FAB654BBD35FCEB62952DAF5F648B9CFE6AEB6C562C2FF4AF495196F4D7F1745AAD97DC3A6BDBBA98ACDB1C7FD8EE8F1EAF89E08DFC387A4CAC77362394EEDA21CE72FC724934E88D7259B54FC3819645BE6C05EF237E8DBE0C3ED4263CCEF67A951F1D3FFDE2ECC5E3BBFCFB639AEF655BB4D72775CEBF656513FB3040D37CED7E3B2E8BAC39BA7E7C37FC40BF3F2F689CD52AAF338CA5A9AB6AF17B4E5AA2EA92B8052268DEE386E6A5BC065D57D9343F7AF2E6E4CB2F6C23EF0BD7210DD343D6FD7ED7FFE30D0DF92E64803E6A7F629D37C0C77E70BC6CAEF29A67CA03B5CCAF7C327C20E40E348BFE0D1F361D5E528EFCBD17E5ABFCFCAE20E998BBCBBF7785D17E8ED8EDE9E9B3E3AF9EBFF9A133DCBD7BF9F2A2CE2FDABC9AFD88DD7EC46E3F1476FB11BFFD9CF1DB5DB5AA06FCCB329B923966EFCF9FA9EEEB3DB2F7E0C721CEAB72F6A67A93D78BA3731A0231A6F7C9E3699E35F92BFAA75AF6E0057E23885CB427593D3B5EB7F3AA2E1AB192D4705557B3F5B4E5FECEDA7C81CFD4553B66C171DF0C7DE1F8FA887C2BFBFBE3B9F61EBCA5CEAB3A30D62DD42F1FD7F92FC2CC076DCDB4BC365E59D828EE9C29DC936A961F7DFBCBD76FBE7C79766C7BE34F4D13F244F7EEDBAF3CFFF54CBD70FAE8C1EEDEA7F7EF3FD8DB7EF860BAB3FDE9F9DEDE76BEB3BF4F0EF0EEFEFEF9A7D96CE7D37D07C2BDD70515E12C6D71BA9CBDAC8A65FB555D1E697CB0BB333E78307EB03FDEDBD97BF4E9EEEEEE83BB4FDE8CCF5EFFE4F12C5BB5793DFEEEB75F874EF5DDDE27E3E6726A11F3FB20FE274552321FB062BB6BA6AB682E5FE5CD8AD823C77CC86F4C31A00CEA7B1F3DCEEBBAAAF1EB5DFDFD69DE4CEB62A5C27B3780E604D0FBDD2AED254FA051D74B929423E74093DCE383C79759B9CE9DBB2D7FD25B1EB4F7012C3C1402B7DE7F04B8FBDDF1D8ABAAECFC4574A37FCDA45B15830FA1E58C776CFFE66F8EDF973867AF8F4FDE9CFDE4F19BD3A7E1009E1D3F7F7DFAF569F3FCECE4F4C5EBD337BFCFCBD310EED9EB9710A5DD873B0FBF3EF4A75F7E717CF6E2ECC5B32F5F1EBF7EFDDD2F5F85C8DFFDDA205F1C7F11E2FB7540F1D84F4E7FFFFEE067F979B62EDBAF3FF02F5F7D7EFCE2ECA78EDF9C7DF9E2AC33631FCCCF67AF7FFFE3AFDE101DFACC207623C6CB5DAE337A4258D8FFCBFBB2A7FC6F340ABBFFAF350AA74F7FFFD3DFFBE4DBC72F3E3F1DB60DF7DEC336ECFEBFC6367CFBB46B1B7A9FFCC836FCFFD7367CF5FAF4D5F3A7C72F5F1EBFF97638027C4A0C74F2E2B34DD992D1975F7DA684C4AF143DC877F8635E352DB9A8A3A7279F7DFB14FF96D5342BBF3EBD07EDD89B575FFD6C98B1E7676F3E002CB992AF8E3FEF80BCBFF3F501D2F065CABFE1A1FFEC58B197CF8FFB96F6A82CDA3CFD1022FCBC358E7B3F328E3F328E3F328E56B0361B479B78FB2198C77BC3C9B61F59C79F5BEBF84D04B83F328FFF2F308F035FE015C980FE3F77D83928591F0000

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

有人可以指导我在sql server 2005中将上面的varbinary（max）转换为xml格式（解密）吗？

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

当我尝试将数据转换为xml时，我收到此错误：

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

XML解析：第0行，字符0，无法识别的输入签名

2 个解决方案

#1

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

我正在使用它，效果很好

#2

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

亚历克斯是对的。如果您将二进制文件写出来并解压缩，则它包含一个XML文件的文件。

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.

你用它做什么取决于你的需要。例如，如果您只需要这个文件，可以将其复制到您选择的十六进制编辑器中并保存。然后使用gzip客户端解压缩它。

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

另一方面，如果您需要迁移数据，则将取决于您的功能和要求

#1