Import & Concatenate XML Documents in SQL Server

To prepare for next blog post (XML Inner Join using XQuery), today, we'll learn how to import and concatenate XML documents in SQL Server.

USE tempdb;
GO

-- Import XML documents as CLOB = Character Large Object. 
-- Note: AS of SQL 2012, XML data type can hold 2GB of data max. 
DECLARE @customers XML = 
(SELECT * FROM OPENROWSET(BULK 'C:\Self_Dev\XML\customers.xml', SINGLE_CLOB) AS customers);

DECLARE @orders XML =
(SELECT * FROM OPENROWSET(BULK 'C:\Self_Dev\XML\orders.xml', SINGLE_CLOB) AS orders);

SELECT @customers AS customers, @orders AS orders;

-- Concatenate XML document.
-- This query will fails. Operand data type xml is invalid for add operator.
DECLARE @customers_orders XML = @customers + @orders;


-- Concatenate XML document using XML PATH mode.
-- Add the default row tag as root node
DECLARE @customers_orders2 XML = (SELECT @customers, @orders
                                 FOR XML PATH);
SELECT @customers_orders;

-- Concatenate XML document,
-- an empty string as argument in the PATH() will remove the default row tag  
DECLARE @customers_orders3 XML = (SELECT @customers, @orders
                                 FOR XML PATH(''));
SELECT @customers_orders3;

-- Concatenate XML document,
-- and provide a specific root node.
DECLARE @customers_orders4 XML = (SELECT @customers, @orders
                                 FOR XML PATH('customersorders'));
SELECT @customers_orders4;
GO

Result for @customers_orders4

--
-- Another option: using VARCHAR data type. 
DECLARE @customers VARCHAR(MAX) = 
(SELECT * FROM OPENROWSET(BULK 'C:\Self_Dev\XML\customers.xml', SINGLE_CLOB) AS customers);

DECLARE @orders VARCHAR(MAX) =
(SELECT * FROM OPENROWSET(BULK 'C:\Self_Dev\XML\orders.xml', SINGLE_CLOB) AS orders);

DECLARE @customers_orders XML = @customers + @orders;

SELECT @customers_orders;
GO

Next couple posts, we'll learn how inner and outer join work in XQuery.

Reference:
OPENROWSET
XML PATH mode
XML Data Type
LOBs, BLOBs, CLOBs