将 varbinary(max) 转换为 xml 格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6510251/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
To convert varbinary(max) to xml format
提问by umapathy
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，无法识别的输入签名
回答by Gisli
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
我用这个效果很好
回答by Conrad Frix
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
另一方面，如果您需要迁移数据，这将取决于您的能力和要求
回答by DIBYA RANJAN TRIPATHY
In SQL Server 2016, We have an option of COMPRESS(...) and DECOMPRESS(...) function to convert XML to varbinary(max) and vice-versa.
在 SQL Server 2016 中，我们可以选择 COMPRESS(...) 和 DECOMPRESS(...) 函数来将 XML 转换为 varbinary(max)，反之亦然。
