XML Data – Complete Conceptual Guide (SQL Server Perspective)

How insert data on table from XML in SQL server, example: There are two method for insert data in table from XML. DECLARE @XMLInput VARCHAR(MAX); SET @XMLI…

Author

SQLDataDev Editorial Team

Mar 19, 2026 6 min read

How insert data on table from XML in SQL server, example:

There are two method for insert data in table from XML.

DECLARE @XMLInput VARCHAR(MAX);

SET @XMLInput = '
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';

-- if @XMLInput (variable) is varchar or nvarchar then we need to change data type from varchar to xml.
DECLARE @xml xml = CAST(@XMLInput AS XML);

-- Select CustomerID and ContactName from the XML
SELECT 
    t.C.value('@CustomerID', 'VARCHAR(50)') AS CustomerID,
    t.C.value('@ContactName', 'VARCHAR(50)') AS ContactName
FROM @xml.nodes('/ROOT/Customer') as T(C);

-- Select Order details from the XML
SELECT 
    C.value('@CustomerID', 'VARCHAR(50)') AS CustomerID,
    C.value('@EmployeeID', 'INT') AS EmployeeID,
    C.value('@OrderDate', 'DATETIME') AS OrderDate,
    od.value('@OrderID', 'INT') AS OrderID,
    od.value('@ProductID', 'INT') AS ProductID,
    od.value('@Quantity', 'INT') AS Quantity
FROM @xml.nodes('/ROOT/Customer/Order') as T(C)
CROSS APPLY t.C.nodes('OrderDetail') as O(od);

DECLARE @XMLInput VARCHAR(MAX);
DECLARE @idoc INT;

SET @XMLInput = '
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';

-- Prepare the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLInput;

-- Select Customer details.
SELECT 
    CustomerID,
    ContactName
FROM OPENXML(@idoc, '/ROOT/Customer', 1)
WITH (
    CustomerID VARCHAR(50) '@CustomerID',
    ContactName VARCHAR(50) '@ContactName'
);

-- Select Order details.
SELECT 
    CustomerID,
    EmployeeID,
    OrderDate,
    OrderID,
    ProductID,
    Quantity
FROM OPENXML(@idoc, '/ROOT/Customer/Order', 2)
WITH (
    CustomerID VARCHAR(50) '@CustomerID',
    EmployeeID INT '@EmployeeID',
    OrderDate DATETIME '@OrderDate',
    OrderID INT 'OrderDetail/@OrderID',
    ProductID INT 'OrderDetail/@ProductID',
    Quantity INT 'OrderDetail/@Quantity'
);

-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @idoc;

--Ex.

ALTER PROCEDURE InsertCityDataFromXML
    @XMLData NVARCHAR(MAX)='<Countries>
    <Country>
        <CountryName>United States</CountryName>
        <States>
            <State>
                <StateName>California</StateName>
                <Cities>
                    <City>
                        <CityName>Los Angeles</CityName>
                        <IsCapital>false</IsCapital>
                    </City>
                    <City>
                        <CityName>San Francisco</CityName>
                        <IsCapital>false</IsCapital>
                    </City>
                    <City>
                        <CityName>Sacramento</CityName>
                        <IsCapital>true</IsCapital>
                    </City>
                </Cities>
            </State>
            <State>
                <StateName>Texas</StateName>
                <Cities>
                    <City>
                        <CityName>Houston</CityName>
                        <IsCapital>false</IsCapital>
                    </City>
                    <City>
                        <CityName>Austin</CityName>
                        <IsCapital>true</IsCapital>
                    </City>
                    <City>
                        <CityName>Dallas</CityName>
                        <IsCapital>false</IsCapital>
                    </City>
                </Cities>
            </State>
        </States>
    </Country>

    <Country>
        <CountryName>India</CountryName>
        <States>
            <State>
                <StateName>Maharashtra</StateName>
                <Cities>
                    <City>
                        <CityName>Mumbai</CityName>
                        <IsCapital>true</IsCapital>
                    </City>
                    <City>
                        <CityName>Pune</CityName>
                        <IsCapital>false</IsCapital>
                    </City>
                </Cities>
            </State>
            <State>
                <StateName>Uttar Pradesh</StateName>
                <Cities>
                    <City>
                        <CityName>Lucknow</CityName>
                        <IsCapital>true</IsCapital>
                    </City>
                    <City>
                        <CityName>Kanpur</CityName>
                        <IsCapital>false</IsCapital>
                    </City>
                </Cities>
            </State>
        </States>
    </Country>
</Countries>
'
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    
    BEGIN TRY

	    BEGIN TRANSACTION;
        -- Parse XML data
        DECLARE @XMLHandle XML = CAST(@XMLData AS XML);

        -- Table variable to store parsed data
        DECLARE @ALl TABLE (
            CountryName NVARCHAR(100),
            StateName NVARCHAR(100),
            CityName NVARCHAR(100),
            IsCapital BIT
        );

        -- Inserting parsed XML data into CityTable
        INSERT INTO @ALl (CountryName, StateName, CityName, IsCapital)
        SELECT
            c.value('(CountryName/text())', 'NVARCHAR(100)') AS CountryName,
            s.value('(StateName/text())', 'NVARCHAR(100)') AS StateName,
            ci.value('(CityName/text())', 'NVARCHAR(100)') AS CityName,
            CASE WHEN ci.value('(IsCapital/text())', 'NVARCHAR(10)') = 'true' THEN 1 ELSE 0 END AS IsCapital
        FROM @XMLHandle.nodes('/Countries/Country') AS country(c)
        CROSS APPLY c.nodes('States/State') AS state(s)
        CROSS APPLY s.nodes('Cities/City') AS city(ci);

       -- Table variables to store parsed data
        DECLARE @CountryTable TABLE (
            CountryID INT IDENTITY(1,1) PRIMARY KEY,
            CountryName NVARCHAR(100) UNIQUE
        );

        DECLARE @StateTable TABLE (
            StateID INT IDENTITY(1,1) PRIMARY KEY,
            CountryID INT,
            StateName NVARCHAR(100)
        );

        DECLARE @CityTable TABLE (
            CityID INT IDENTITY(1,1) PRIMARY KEY,
            StateID INT,
            CityName NVARCHAR(100),
            IsCapital BIT
        );

        -- Insert into CountryTable
        INSERT INTO @CountryTable (CountryName)
        SELECT DISTINCT c.value('(CountryName/text())', 'NVARCHAR(100)')
        FROM @XMLHandle.nodes('/Countries/Country') AS country(c);

        -- Insert into StateTable
        INSERT INTO @StateTable (CountryID, StateName)
        SELECT DISTINCT
            ct.CountryID,
            s.value('(StateName/text())', 'NVARCHAR(100)')
        FROM @XMLHandle.nodes('/Countries/Country') AS country(c)
        CROSS APPLY c.nodes('States/State') AS state(s)
        JOIN @CountryTable ct ON ct.CountryName = c.value('(CountryName/text())', 'NVARCHAR(100)');

        -- Insert into CityTable
        INSERT INTO @CityTable (StateID, CityName, IsCapital)
        SELECT DISTINCT
            st.StateID,
            ci.value('(CityName/text())', 'NVARCHAR(100)'),
            CASE WHEN ci.value('(IsCapital/text())', 'NVARCHAR(10)') = 'true' THEN 1 ELSE 0 END
        FROM @XMLHandle.nodes('/Countries/Country') AS country(c)
        CROSS APPLY c.nodes('States/State') AS state(s)
        CROSS APPLY s.nodes('Cities/City') AS city(ci)
        JOIN @StateTable st ON st.StateName = s.value('(StateName/text())', 'NVARCHAR(100)');


		COMMIT TRANSACTION;
        -- Return the table data
		SELECT * FROM @ALl
        SELECT * FROM @CountryTable;
        SELECT * FROM @StateTable;
        SELECT * FROM @CityTable;
    END TRY
    BEGIN CATCH
        -- Capture error details
        SET @ErrorMessage = ERROR_MESSAGE();
        SET @ErrorSeverity = ERROR_SEVERITY();
        SET @ErrorState = ERROR_STATE();
        
        -- Rollback the transaction in case of error
        ROLLBACK TRANSACTION;
        
        -- Raise the error for debugging
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;
END;

GO
EXEC InsertCityDataFromXML

SELECT DISTINCT
            ci.value('(../../../../CountryName/text())','NVARCHAR(100)') CountryName,
			ci.value('(../../StateName/text())', 'NVARCHAR(100)') StateName,
            ci.value('(CityName/text())', 'NVARCHAR(100)') CityName,
            CASE WHEN ci.value('(IsCapital/text())', 'NVARCHAR(10)') = 'true' THEN 1 ELSE 0 END IsCapital
        FROM @XMLHandle.nodes('/Countries/Country/States/State/Cities/City') AS city(ci)

SQL Server XML ke ALL IMPORTANT METHODS basic → advance tak clear examples ke saath diye gaye hain.

Base XML (Example jo hum use karenge)

DECLARE @X XML = &#039;
<Employees>
  <Employee id=&#034;1&#034;>
    <Name>Boss</Name>
    <Dept>IT</Dept>
    <Salary>80000</Salary>
    <Skills>
      <Skill>SQL</Skill>
      <Skill>Python</Skill>
    </Skills>
  </Employee>
  <Employee id=&#034;2&#034;>
    <Name>Aman</Name>
    <Dept>HR</Dept>
    <Salary>50000</Salary>
  </Employee>
</Employees>&#039;;

🔥 XML METHODS IN SQL SERVER (COMPLETE LIST)

1️⃣ `.nodes()` – XML ko ROWS me todna (MOST IMPORTANT)

👉 Kab use hota hai?

Jab XML me repeating nodes hote hain.

SELECT
    E.value(&#039;(Name)&#091;1]&#039;, &#039;VARCHAR(50)&#039;) AS Name
FROM @X.nodes(&#039;/Employees/Employee&#039;) AS T(E);

✔ XML → Relational rows
✔ Interview favorite question

2️⃣ `.value()` – Single scalar value nikalna

👉 Rule:

Sirf ONE value
mandatory

SELECT
    E.value(&#039;(Salary)&#091;1]&#039;, &#039;INT&#039;) AS Salary
FROM @X.nodes(&#039;/Employees/Employee&#039;) AS T(E);

❌ Multiple values allowed nahi
❌ miss → error

3️⃣ `.query()` – XML ka ek hissa nikalna

👉 Jab output bhi XML chahiye

SELECT
    E.query(&#039;Skills&#039;) AS SkillXML
FROM @X.nodes(&#039;/Employees/Employee&#039;) AS T(E);

✔ XML fragment return karta hai
✔ .value() se different

4️⃣ `.exist()` – Node exist karta hai ya nahi

👉 WHERE condition / validation ke liye

SELECT
    E.exist(&#039;Skills&#039;) AS HasSkills
FROM @X.nodes(&#039;/Employees/Employee&#039;) AS T(E);

Return:

1 → exist
0 → nahi

5️⃣ `.modify()` – XML update karna (DANGEROUS but powerful)

👉 XML ke andar update / insert / delete

🔹 Update value

SET @X.modify(&#039;
  replace value of
  (/Employees/Employee&#091;1]/Salary/text())&#091;1]
  with &#034;90000&#034;
&#039;);

🔹 Insert new node

SET @X.modify(&#039;
  insert <Age>30</Age>
  into (/Employees/Employee&#091;1])&#091;1]
&#039;);

🔹 Delete node

SET @X.modify(&#039;
  delete (/Employees/Employee&#091;2]/Dept)&#091;1]
&#039;);

⚠ Use carefully
⚠ Performance heavy

6️⃣ `.nodes()` + `.value()` (STANDARD PATTERN)

👉 99% real projects me yahi use hota hai

SELECT
    E.value(&#039;@id&#039;, &#039;INT&#039;) AS EmpID,      -- Attribute
    E.value(&#039;(Name)&#091;1]&#039;, &#039;VARCHAR(50)&#039;),
    E.value(&#039;(Dept)&#091;1]&#039;, &#039;VARCHAR(20)&#039;)
FROM @X.nodes(&#039;/Employees/Employee&#039;) AS T(E);

7️⃣ Attribute access (`@` symbol)

SELECT
    E.value(&#039;@id&#039;, &#039;INT&#039;) AS EmpID
FROM @X.nodes(&#039;/Employees/Employee&#039;) AS T(E);

👉 @ = attribute

8️⃣ Nested XML handle karna

Skills ko rows me todna

SELECT
    E.value(&#039;(Name)&#091;1]&#039;, &#039;VARCHAR(50)&#039;) AS Name,
    S.value(&#039;.&#039;, &#039;VARCHAR(20)&#039;) AS Skill
FROM @X.nodes(&#039;/Employees/Employee&#039;) AS T(E)
CROSS APPLY E.nodes(&#039;Skills/Skill&#039;) AS S(S);

🔥 Advanced XML shredding

9️⃣ `.value()` vs `.query()` (VERY COMMON INTERVIEW)

Method	Output
`.value()`	Scalar (INT, VARCHAR)
`.query()`	XML
`.nodes()`	Rowset
`.exist()`	0 / 1
`.modify()`	Update XML

🔟 XML methods summary table

Method	Purpose
`.nodes()`	XML → Rows
`.value()`	Single value
`.query()`	XML fragment
`.exist()`	Node check
`.modify()`	Update XML

🎯 REAL-LIFE USE CASES

✔ API XML → Table

INSERT INTO Employees
SELECT
 E.value(&#039;(Name)&#091;1]&#039;, &#039;VARCHAR(50)&#039;),
 E.value(&#039;(Dept)&#091;1]&#039;, &#039;VARCHAR(20)&#039;),
 E.value(&#039;(Salary)&#091;1]&#039;, &#039;INT&#039;)
FROM @X.nodes(&#039;/Employees/Employee&#039;) AS T(E);

❌ COMMON MISTAKES (Interview trap)

❌ .value() without
❌ .nodes() skip karna
❌ Heavy updates with .modify()

Topics covered

Keep building your data skillset

Explore more SQL, Python, analytics, and engineering tutorials.

Browse All Posts