SQL XML Output with PDFs Attached

Question

I have a SQL Server procedure written that exports data in XML format. In the database, I also have PDFs that have been stored as BLOB files that I need to export with the data. Is it possible to convert these to PDF as I export?

I want to export them within the XML file as PDF..is that possible? Sorry if that's not the right phrasing. — Sarah B, Jun 17 '16 at 16:46
[How do you embed binary data in XML?](http://stackoverflow.com/a/19904/5690722) [Handling Binary Data in XML Documents](http://www.xml.com/pub/a/98/07/binary/binary.html) [Base64 encoding in SQL Server 2005 T-SQL](http://stackoverflow.com/a/8247117/5690722) — Y.B., Jun 17 '16 at 17:00

score 0 · Accepted Answer · answered Jun 20 '16 at 09:06

This is really easy...

I assume, that the BLOBs live in a table column of type VARBINARY(MAX). Including such a column into a SELECT ... FOR XML PATH will implicitly do the conversion for you.

In this example I use three tiny binary BLOBs, put them into a XML-variable and re-read them. There should be not difference with your PDF BLOBs:

DECLARE @tbl TABLE(ID INT,Content VARBINARY(MAX));
INSERT INTO @tbl VALUES
 (1,0x101010101010101010101)
,(2,0x110011001100110011001100)
,(3,0x111000111000111000111000);

DECLARE @xml XML=
(
    SELECT ID AS [@ID]
          ,Content
    FROM @tbl
    FOR XML PATH('myData'),ROOT('root')
);

SELECT @xml;

The result as XML (implicit conversion to base64)

<root>
  <myData ID="1">
    <Content>AQEBAQEBAQEBAQE=</Content>
  </myData>
  <myData ID="2">
    <Content>EQARABEAEQARABEA</Content>
  </myData>
  <myData ID="3">
    <Content>ERAAERAAERAAERAA</Content>
  </myData>
</root>

Now I read the data from the XML

SELECT B.value('Content[1]','varbinary(max)') AS BackToVarbinary
FROM @xml.nodes('/root/myData') AS A(B)

The result

BackToVarbinary
0x0101010101010101010101
0x110011001100110011001100
0x111000111000111000111000

SQL XML Output with PDFs Attached

1 Answers1