export binary data to xml and read back the same in sql server

Question

We have a requirement to send varbinary data in xml as a tag and we we have to read that xml in another sql server db and we have to receive the source data, but I'm not able to make it.

Here is the piece of code:

DECLARE @cmds NVARCHAR(MAX)
DECLARE @obfoo VARBINARY(MAX)
DECLARE @ks XML,@hDoc INT
--convert some text to binary?
SET @cmds = N'HelloWorld!'
SET @obfoo = CAST(@cmds AS VARBINARY(MAX))

SELECT 'Source data',@cmds,@obfoo as source_data

SELECT @ks =
(SELECT  @obfoo
FOR XML PATH('data'))

SELECT @ks

EXEC sp_xml_preparedocument @hDoc OUTPUT, @ks

select @obfoo = 
CONVERT(varbinary(max),data)
FROM OPENXML(@hdoc, '/',2)
WITH    
(
    data        nvarchar(max) 'data'
)

SELECT 'Target_data', CAST(@obfoo AS NVARCHAR(MAX) ),@obfoo

***but I'm not able to make it*** -- What exactly do you mean by this? What problems are you facing? — iamdave, Feb 09 '18 at 13:32
`FROM OPENXML` with the corresponding SPs to prepare and to remove a document is outdated and should not be used any more. Rather use the appropriate [methods the XML data type provides](https://msdn.microsoft.com/en-us/library/ms190798.aspx). — Shnugo, Feb 09 '18 at 21:38

Shnugo · Answer 1 · 2018-02-12T07:57:30.423

Selecting a binary into XML will be transformed to a base64 encoded string.

DECLARE @SomeString NVARCHAR(10)='abcdefg';
DECLARE @SomeBinary VARBINARY(MAX)=CAST(@SomeString AS VARBINARY(MAX));
DECLARE @Xml XML=(SELECT @SomeBinary FOR XML PATH('row'),ROOT('root'),TYPE);
SELECT @xml;

-- The Binary is implicitly converted to base64

<root>
  <row>YQBiAGMAZABlAGYAZwA=</row>
</root>

--Reading this, will implicitly re-encode this:

SELECT CAST(@xml.value(N'(/root/row/text())[1]',N'varbinary(max)') AS NVARCHAR(MAX));

UPDATE

Your restriction to use FROM OPENXML is something you really should discuss! If you were restricted to use a horse for your next far distance travel, you would discuss this too :-D

Your code above returns a base64 encoded value

SELECT @ks

returns

<data>SABlAGwAbABvAFcAbwByAGwAZAAhAA==</data>

Read this documentation about FROM OPENXML and binary data (section K). The advice is: Use XML's .value() method to convert this back!

EXEC sp_xml_preparedocument @hDoc OUTPUT, @ks

DECLARE @base64String AS NVARCHAR(MAX);
select @base64String = data
FROM OPENXML(@hdoc, '/',2)
WITH    
(
    data        varbinary(max) 'data'
)

SELECT 'Target_data', @base64String, CAST(CAST(N'<x>' + @base64String + N'</x>' AS XML).value(N'.',N'varbinary(max)') AS NVARCHAR(MAX));

EXEC sp_xml_removedocument @hDoc;

This works, but is a rather stupid approach. Why not read the value directly out of your XML with the .value() method? This is like a far-distance-travel with a horse and an aeroplane for the last mile...

FROM OPENXML has a lot of restrictions, is slow, needs to parse the full XML whenever you need it, is not inlineable, not useable in a VIEW or an iTVF, clumsy, hard to read and - uhm - outdated.

UPDATE 2

In the comments I've told you already that your restrictions point to some absolutely outdated settings. You should rather change those...

If you have to stick with it, you might insert the binary value as hexString into your XML and use a dynamically created statement to get the value back:

DECLARE @hexString VARCHAR(1000)=master.sys.fn_varbintohexstr(CAST('Some text you want to convert!' AS VARBINARY(1000)));
SELECT @hexString;
DECLARE @cmd VARCHAR(MAX)='SELECT CAST(' + @hexString + ' AS VARCHAR(1000))';
EXEC(@cmd);

But this is a really ugly hack... Have to clean my hands now :-D

Hi Shnugo, Thank you so much for your response. The solution which you provided in working, But we have a restriction to use only openxml. Is there any possibility to make it in openxml? — Vijay A, Feb 12 '18 at 06:49
Thank you so much shnugo, By default our db is set to Ansi warnings off, That was the reason we said like we are restricted. We'll try using the first method only — Vijay A, Feb 12 '18 at 07:32
Thank you so much shnugo, Why I said we are restricted is because of below Issue, Msg 1934, Level 16, State 1, Line 24 SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. Because our application DB settings required ANSI warnings off — Vijay A, Feb 12 '18 at 07:38
@VijayA You've tagged your question with `sql-server-2012`. I assume, that your target db is very old, has started around SQL Server 2000 and was never *modernized*, just upgraded. You should read about these settings. Check the settings of a newly created default database and try to use the same for your database. — Shnugo, Feb 12 '18 at 07:45
@VijayA If you have to stick with this, you can try to insert the binary as HexString. See my update — Shnugo, Feb 12 '18 at 07:57

export binary data to xml and read back the same in sql server

1 Answers1

UPDATE

UPDATE 2