CODEWITHSUNDEEP
sqlsql-serverxmlxpathxquery
cyclington
cyclington

Reputation: 48

SQL Server 2012 create list of VARBINARY in single XML element

I am currently struggling to find a solution to the following problem.

I have a result set of VARBINARY values - for example:

these results Need to be packed into a XML Element delimited by a single space (to siginify an array of values). Example of how the result should look:

<results>QAAAAAAAAAE= QAAAAAAAAAQ=</results>

The issue I am having while using XML PATH is that I cannot combine a ' ' (varchar) and the result varbinary field. If I add the ' ' before the result and then convert to varbinary the result is incorrect due to having converted the space as well. Here is an example of what I have attempted thus far:

(
    STUFF((SELECT DISTINCT ' ' + CONVERT( VARBINARY, id)
    FROM results
    FOR XML PATH('ns2:children')
    ),1,1,'')
),

Upvotes: 1

Views: 302

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

You should not deal with XML on string level. This might have various side effects...

You can try this:

I fill a table with some values in a VARBINARY column:

DECLARE @table TABLE(SomeData VARBINARY(MAX));
INSERT INTO @table VALUES(0x12AF)
                        ,(CAST('test' AS VARBINARY(MAX)))
                        ,(CAST(GETDATE() AS VARBINARY(MAX)));
SELECT * 
FROM @table;

The result

SomeData
0x12AF
0x74657374
0x0000A81100AD9F69

If you get this as XML, the conversion to base64 is done implicitly

SELECT * FROM @table FOR XML PATH('result')

the result

<result>
  <SomeData>Eq8=</SomeData>
</result>
<result>
  <SomeData>dGVzdA==</SomeData>
</result>
<result>
  <SomeData>AACoEQCtn2k=</SomeData>
</result>

Now there is XQuery-function data() to your rescue. It will automatically concatenate all sub-values separated by a blank:

SELECT(
       SELECT * 
       FROM @table 
       FOR XML PATH('result'),TYPE
       ).query('data(/result/SomeData)') AS result 
FOR XML PATH('results')

The result is 

<results>
  <result>Eq8= dGVzdA== AACoEQCtn2k=</result>
</results>

Upvotes: 1

shA.t
shA.t

Reputation: 16978

If I understand what do you want?! A way to get <results>QAAAAAAAAAE= QAAAAAAAAAQ=</results> as result is:

select ltrim((
    select ' '+cast(id as varchar(50))
    from t
    group by id
    for xml path(''))) results
for xml path('');

SQL Fiddle Demo

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Pls. try short of SQL Command which could help u to achieve the above result :

SELECT REPLACE(T.Data, '</results><results>', ' ')
FROM
(
    SELECT STUFF(
                (
                    SELECT DATA [results]
                    FROM <table_name> FOR XML PATH('')
                ), 1, 1, '<') [Data]
) T;

Result :

<results>QAAAAAAAAAE= QAAAAAAAAAQ=</results>

Upvotes: 0

Related Questions