Create XML From Tables

What we have in the input file is shown below. After that, we show what we need the XML file to look like. We can use XQuery insert commands to extract the data from the tables and insert it into a “template file”. This post discusses the stored procedure CreateXMLData.

ABC Book Supply Inc.,ABC0001,00002,20170402113035,
"Microsoft SQL Server 2016: A Beginner's Guide Sixth Edition",1259641791,Dusan Petovick & nobody else,1,$32.12
"Beginning Microsoft SQL Server 2012 Programming",1118102282, Paul Atkinson & Robert Vieira,2,39.99

<bookstoreorders>
	<supplier>
		<name>ABC Book Supply Inc.</name>
		<suppliercode>ABC0001</suppliercode>
		<order>
			<orderid>00002</orderid>
			<orderdatetime>20170402113035</orderdatetime>
		</order>
	</supplier>
	<books>
		<book>
			<title>Microsoft SQL Server 2016: A Beginner's Guide Sixth Edition</title>
			<ISBN>1259641791</ISBN>
			<authors>Dusan Petovick &amp; nobody else</authors>
			<quantity>1</quantity>
			<unitprice>32.12</unitprice>
		</book>
		<book>
			<title>Beginning Microsoft SQL Server 2012 Programming</title>
			<ISBN>1118102282</ISBN>
			<authors>Paul Atkinson & Robert Vieira</authors>
			<quantity>2</quantity>
			<unitprice>39.99</unitprice>
		</book>
	<books>
</bookstoreorders>

Here is part of the SQL Server code that accomplishes the above tasks. After we build the xml data, we can insert it into a table called [XmlData].

DECLARE @xmlSupplier XML
DECLARE @xmlOrder XML
DECLARE @xmlBooks XML
DECLARE @xmlALL XML
SET @xmlALL = '<bookstoreorders><books></books></bookstoreorders>'
SET @xmlSupplier = (SELECT [FullSupplierName]
	  ,[SupplierCode]
  FROM [ILoveBooks].[dbo].[Suppliers]
  FOR XML PATH ('supplier'));
SET @xmlALL.modify('insert sql:variable("@xmlSupplier") as first into (/bookstoreorders)[1]')
-------------------------------------------------------------------------
SET @xmlOrder = (SELECT [OrderNumber]
		,[TotalUnits]
		,[TotalValue]
		,[CSVDatetime]
		FROM [ILoveBooks].[dbo].[Orders]
		FOR XML PATH ('order'));
SET @xmlALL.modify('insert sql:variable("@xmlOrder") as last into (/bookstoreorders/supplier)[1]')
-------------------------------------------------------------------------
SET @xmlBooks = (SELECT [BookTitle]
		,[ISBN]
		,[Authors]
		,[Quantity]
		,[UnitPrice]
FROM [ILoveBooks].[dbo].[Books]
FOR XML PATH ('book'));
SET @xmlALL.modify('insert sql:variable("@xmlBooks") as first into (/bookstoreorders/books)[1]')
SELECT @xmlALL;
---------------------------------------------------------------
-- Now we have the xml in the variable @xmlALL
-- Save it into the table [XmlData].
DECLARE @SupplierCode VARCHAR(7)
DECLARE @OrderNumber VARCHAR(5)

SELECT @SupplierCode = SupplierCode FROM Suppliers;
SELECT @OrderNumber = OrderNumber FROM Orders;
INSERT INTO [ILoveBooks].[dbo].[XmlData] (SupplierCode, OrderNumber, XmlData)
	VALUES (@SupplierCode, @OrderNumber, @xmlALL)

Below is the xml produced by the above code.

<bookstoreorders>
  <supplier>
    <FullSupplierName>ABC Book Supply Inc.</FullSupplierName>
    <SupplierCode>ABC0001</SupplierCode>
    <order>
      <OrderNumber>00002</OrderNumber>
      <TotalUnits>3</TotalUnits>
      <TotalValue>112.1000</TotalValue>
      <CSVDatetime>20170402113035</CSVDatetime>
    </order>
  </supplier>
  <books>
    <book>
      <BookTitle>Microsoft SQL Server 2016: A Beginner's Guide Sixth Edition</BookTitle>
      <ISBN>1259641791</ISBN>
      <Authors>Dusan Petovick &amp; nobody else</Authors>
      <Quantity>1</Quantity>
      <UnitPrice>32.1200</UnitPrice>
    </book>
    <book>
      <BookTitle>Beginning Microsoft SQL Server 2012 Programming</BookTitle>
      <ISBN>1118102282</ISBN>
      <Authors>Paul Atkinson &amp; Robert Vieira</Authors>
      <Quantity>2</Quantity>
      <UnitPrice>39.9900</UnitPrice>
    </book>
  </books>
</bookstoreorders>