Convert XML To Table SQL Server

In this article, we will learn how we can convert XML to a table in SQL Server. XML is one of the most common formats used to share information between different platforms. Sometimes we have a scenario where we get the xml in response as the old apis which were used earlier before the introduction of json were using the XML. So, sql have provided us the functionality for using the XML directly in the query. Let see how we can use that.

Let’s first generate some sample dummy data XML File which we will be converting to a table.

DECLARE @XML VARCHAR(MAX) = '<OrderDetails>
  <row>
    <OrderID>10248</OrderID>
    <CustomerID>VINET</CustomerID>
    <EmployeeID>5</EmployeeID>
    <OrderDate>2018-07-04T00:00:00</OrderDate>
    <RequiredDate>2018-08-01T00:00:00</RequiredDate>
    <ShippedDate>2018-07-16T00:00:00</ShippedDate>
    <ShipVia>3</ShipVia>
    <Freight>32.38</Freight>
    <ShipName>Vins et alcools Chevalier</ShipName>
    <ShipAddress>59 rue de l&#x27;Abbaye</ShipAddress>
    <ShipCity>Reims</ShipCity>
    <ShipRegion/>
    <ShipPostalCode>51100</ShipPostalCode>
    <ShipCountry>France</ShipCountry>
    <Customer/>
    <Employee/>
    <Shipper/>
  </row>
  <row>
    <OrderID>10249</OrderID>
    <CustomerID>TOMSP</CustomerID>
    <EmployeeID>6</EmployeeID>
    <OrderDate>2018-07-05T00:00:00</OrderDate>
    <RequiredDate>2018-08-16T00:00:00</RequiredDate>
    <ShippedDate>2018-07-10T00:00:00</ShippedDate>
    <ShipVia>1</ShipVia>
    <Freight>11.61</Freight>
    <ShipName>Toms Spezialitäten</ShipName>
    <ShipAddress>Luisenstr. 48</ShipAddress>
    <ShipCity>Münster</ShipCity>
    <ShipRegion/>
    <ShipPostalCode>44087</ShipPostalCode>
    <ShipCountry>Germany</ShipCountry>
    <Customer/>
    <Employee/>
    <Shipper/>
  </row>
  <row>
    <OrderID>10250</OrderID>
    <CustomerID>HANAR</CustomerID>
    <EmployeeID>4</EmployeeID>
    <OrderDate>2018-07-08T00:00:00</OrderDate>
    <RequiredDate>2018-08-05T00:00:00</RequiredDate>
    <ShippedDate>2018-07-12T00:00:00</ShippedDate>
    <ShipVia>2</ShipVia>
    <Freight>65.83</Freight>
    <ShipName>Hanari Carnes</ShipName>
    <ShipAddress>Rua do Paço, 67</ShipAddress>
    <ShipCity>Rio de Janeiro</ShipCity>
    <ShipRegion>RJ</ShipRegion>
    <ShipPostalCode>05454-876</ShipPostalCode>
    <ShipCountry>Brazil</ShipCountry>
    <Customer/>
    <Employee/>
    <Shipper/>
  </row>
</OrderDetails>'

Let’s write SQL Query to convert the XML to a table.

SELECT Tbl.Col.value('OrderID[1]', 'INT') AS OrderID,
       Tbl.Col.value('CustomerID[1]', 'NVARCHAR(MAX)') AS CustomerID,
       Tbl.Col.value('EmployeeID[1]', 'INT') AS EmployeeID,
       Tbl.Col.value('OrderDate[1]', 'DATETIME') AS OrderDate,
       Tbl.Col.value('RequiredDate[1]', 'DATETIME') AS RequiredDate,
       Tbl.Col.value('ShippedDate[1]', 'DATETIME') AS ShippedDate,
       Tbl.Col.value('ShipVia[1]', 'INT') AS ShipVia,
       Tbl.Col.value('Freight[1]', 'FLOAT') AS Freight,
       Tbl.Col.value('ShipName[1]', 'NVARCHAR(MAX)') AS ShipName,
       Tbl.Col.value('ShipAddress[1]', 'NVARCHAR(MAX)') AS ShipAddress,
       Tbl.Col.value('ShipCity[1]', 'NVARCHAR(MAX)') AS ShipCity,
       Tbl.Col.value('ShipRegion[1]', 'NVARCHAR(MAX)') AS ShipRegion,
       Tbl.Col.value('ShipPostalCode[1]', 'NVARCHAR(MAX)') AS ShipPostalCode,
       Tbl.Col.value('ShipCountry[1]', 'NVARCHAR(MAX)') AS ShipCountry
FROM @XML.nodes('/OrderDetails/row')Tbl(Col);

Execute the above SQL query and check the output.

I hope this article helps you and you will like it.