SQL Server – XML Data to SQL Table using T-SQL

XML to SQL import is useful when structured data is exchanged between systems—such as web services, third-party applications, or legacy tools. It’s beneficial because SQL Server natively supports XML parsing, allowing easy integration without custom ETL tools. A practical example is importing daily sales reports from an e-commerce platform provided in XML format into a central SQL database for analytics and reporting.

Sample XML Data

Year	Region	SalesAmount
2010	India	100
2010	USA	200
2010	Australia	300
2011	India	400
2011	USA	500
2011	Australia	600
2012	India	1000
2012	USA	900
2012	Australia	800

XML Representation of the Above Data

<SalesData>
  <Sale>
    <Year>2010</Year>
    <Region>India</Region>
    <SalesAmount>100</SalesAmount>
  </Sale>
  <Sale>
    <Year>2010</Year>
    <Region>USA</Region>
    <SalesAmount>200</SalesAmount>
  </Sale>
  <Sale>
    <Year>2010</Year>
    <Region>Australia</Region>
    <SalesAmount>300</SalesAmount>
  </Sale>
  <Sale>
    <Year>2011</Year>
    <Region>India</Region>
    <SalesAmount>400</SalesAmount>
  </Sale>
  <Sale>
    <Year>2011</Year>
    <Region>USA</Region>
    <SalesAmount>500</SalesAmount>
  </Sale>
  <Sale>
    <Year>2011</Year>
    <Region>Australia</Region>
    <SalesAmount>600</SalesAmount>
  </Sale>
  <Sale>
    <Year>2012</Year>
    <Region>India</Region>
    <SalesAmount>1000</SalesAmount>
  </Sale>
  <Sale>
    <Year>2012</Year>
    <Region>USA</Region>
    <SalesAmount>900</SalesAmount>
  </Sale>
  <Sale>
    <Year>2012</Year>
    <Region>Australia</Region>
    <SalesAmount>700</SalesAmount>
  </Sale>
</SalesData>

Step-by-Step: Load XML Data into SQL Server Table

Step 1: Create a SQL Table

CREATE TABLE Sales (
    Year INT,
    Region VARCHAR(50),
    SalesAmount INT
);

Step 2: Declare and Load XML into a Variable

DECLARE @xmlData XML

SET @xmlData = '
<SalesData>
  <Sale><Year>2010</Year><Region>India</Region><SalesAmount>100</SalesAmount></Sale>
  <Sale><Year>2010</Year><Region>USA</Region><SalesAmount>200</SalesAmount></Sale>
  <Sale><Year>2010</Year><Region>Australia</Region><SalesAmount>300</SalesAmount></Sale>
  <Sale><Year>2011</Year><Region>India</Region><SalesAmount>400</SalesAmount></Sale>
  <Sale><Year>2011</Year><Region>USA</Region><SalesAmount>500</SalesAmount></Sale>
  <Sale><Year>2011</Year><Region>Australia</Region><SalesAmount>600</SalesAmount></Sale>
  <Sale><Year>2012</Year><Region>India</Region><SalesAmount>1000</SalesAmount></Sale>
  <Sale><Year>2012</Year><Region>USA</Region><SalesAmount>900</SalesAmount></Sale>
  <Sale><Year>2012</Year><Region>Australia</Region><SalesAmount>700</SalesAmount></Sale>
</SalesData>';

Step 3: Insert Data into SQL Table

INSERT INTO dbo.SalesData 
([Year], Region, SalesAmount)
SELECT 
    SalesRow.value('(Year)[1]', 'INT') AS Year,
    SalesRow.value('(Region)[1]', 'VARCHAR(50)') AS Region,
    SalesRow.value('(SalesAmount)[1]', 'INT') AS SalesAmount
FROM 
    @xmlData.nodes('/SalesData/Sale') AS XTbl(SalesRow);

Step 4: Verify the Result

SELECT * FROM Sales;

Related Posts

Leave a Reply Cancel reply