To convert varbinary(max) to xml format

up vote
4
down vote

favorite

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

edited Jan 16 '16 at 16:19

Brian Tompsett - 汤莱恩

4,1631336100

asked Jun 28 '11 at 16:58

umapathy

21112

4

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

@Alex how did you know that 0x1f8b08 was the gzip signature
– Conrad Frix
Jun 28 '11 at 17:37

1

@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

add a comment |

up vote
4
down vote

favorite

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

edited Jan 16 '16 at 16:19

Brian Tompsett - 汤莱恩

4,1631336100

asked Jun 28 '11 at 16:58

umapathy

21112

4

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

@Alex how did you know that 0x1f8b08 was the gzip signature
– Conrad Frix
Jun 28 '11 at 17:37

1

@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

add a comment |

up vote
4
down vote

favorite

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

edited Jan 16 '16 at 16:19

Brian Tompsett - 汤莱恩

4,1631336100

asked Jun 28 '11 at 16:58

umapathy

21112

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

xml sql-server-2005 varbinarymax

edited Jan 16 '16 at 16:19

Brian Tompsett - 汤莱恩

4,1631336100

asked Jun 28 '11 at 16:58

umapathy

21112

edited Jan 16 '16 at 16:19

Brian Tompsett - 汤莱恩

4,1631336100

asked Jun 28 '11 at 16:58

umapathy

21112

edited Jan 16 '16 at 16:19

Brian Tompsett - 汤莱恩

4,1631336100

edited Jan 16 '16 at 16:19

Brian Tompsett - 汤莱恩

4,1631336100

edited Jan 16 '16 at 16:19

Brian Tompsett - 汤莱恩

4,1631336100

asked Jun 28 '11 at 16:58

umapathy

21112

asked Jun 28 '11 at 16:58

umapathy

21112

asked Jun 28 '11 at 16:58

umapathy

21112

4

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

@Alex how did you know that 0x1f8b08 was the gzip signature
– Conrad Frix
Jun 28 '11 at 17:37

1

@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

add a comment |

4

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

@Alex how did you know that 0x1f8b08 was the gzip signature
– Conrad Frix
Jun 28 '11 at 17:37

1

@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

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

@Alex how did you know that 0x1f8b08 was the gzip signature
– Conrad Frix
Jun 28 '11 at 17:37

@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

add a comment |

3 Answers
3

active

oldest

votes

up vote
8
down vote

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 9:49

Gisli

4991825

select CONVERT(xml,(CONVERT(varbinary(max),<ColumnName>))) from <tableName> where <condition>
– Malik Aqib
Dec 26 '16 at 6:48

add a comment |

up vote
1
down vote

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

edited Jun 28 '11 at 17:53

answered Jun 28 '11 at 17:34

Conrad Frix

45.2k1170120

add a comment |

up vote
0
down vote

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 at 10:00

DIBYA RANJAN TRIPATHY

262

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f6510251%2fto-convert-varbinarymax-to-xml-format%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

up vote
8
down vote

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 9:49

Gisli

4991825

select CONVERT(xml,(CONVERT(varbinary(max),<ColumnName>))) from <tableName> where <condition>
– Malik Aqib
Dec 26 '16 at 6:48

add a comment |

up vote
8
down vote

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 9:49

Gisli

4991825

select CONVERT(xml,(CONVERT(varbinary(max),<ColumnName>))) from <tableName> where <condition>
– Malik Aqib
Dec 26 '16 at 6:48

add a comment |

up vote
8
down vote

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 9:49

Gisli

4991825

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 9:49

Gisli

4991825

answered Dec 21 '11 at 9:49

Gisli

4991825

answered Dec 21 '11 at 9:49

Gisli

4991825

answered Dec 21 '11 at 9:49

Gisli

4991825

select CONVERT(xml,(CONVERT(varbinary(max),<ColumnName>))) from <tableName> where <condition>
– Malik Aqib
Dec 26 '16 at 6:48

add a comment |

select CONVERT(xml,(CONVERT(varbinary(max),<ColumnName>))) from <tableName> where <condition>
– Malik Aqib
Dec 26 '16 at 6:48

select CONVERT(xml,(CONVERT(varbinary(max),<ColumnName>))) from <tableName> where <condition>
– Malik Aqib
Dec 26 '16 at 6:48

add a comment |

up vote
1
down vote

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

edited Jun 28 '11 at 17:53

answered Jun 28 '11 at 17:34

Conrad Frix

45.2k1170120

add a comment |

up vote
1
down vote

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

edited Jun 28 '11 at 17:53

answered Jun 28 '11 at 17:34

Conrad Frix

45.2k1170120

add a comment |

up vote
1
down vote

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

edited Jun 28 '11 at 17:53

answered Jun 28 '11 at 17:34

Conrad Frix

45.2k1170120

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

edited Jun 28 '11 at 17:53

answered Jun 28 '11 at 17:34

Conrad Frix

45.2k1170120

edited Jun 28 '11 at 17:53

answered Jun 28 '11 at 17:34

Conrad Frix

45.2k1170120

answered Jun 28 '11 at 17:34

Conrad Frix

45.2k1170120

answered Jun 28 '11 at 17:34

Conrad Frix

45.2k1170120

add a comment |

up vote
0
down vote

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 at 10:00

DIBYA RANJAN TRIPATHY

262

add a comment |

up vote
0
down vote

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 at 10:00

DIBYA RANJAN TRIPATHY

262

add a comment |

up vote
0
down vote

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 at 10:00

DIBYA RANJAN TRIPATHY

262

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 at 10:00

DIBYA RANJAN TRIPATHY

262

answered Nov 20 at 10:00

DIBYA RANJAN TRIPATHY

262

answered Nov 20 at 10:00

DIBYA RANJAN TRIPATHY

262

answered Nov 20 at 10:00

DIBYA RANJAN TRIPATHY

262

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Tukukkk