SQL Server help: How to work with xml type using Query

Sunday, August 30, 2020

How to work with xml type using Query

Working with OPENXML in SQL Server

OPENXML is a Transact-SQL keyword that allows you to treat XML data stored in memory as if it were a relational table or view. This functionality is especially helpful when importing or transforming XML content into tabular format.

To use OPENXML, the sp_xml_preparedocument procedure is first called to parse the XML and return a handle to the document, which can then be referenced in SQL queries.

Example: Populating Tables Using OPENXML

-- Create necessary tables
CREATE TABLE Customers (
    CustomerID varchar(20) PRIMARY KEY,
    ContactName varchar(20),
    CompanyName varchar(20)
);

CREATE TABLE Orders (
    CustomerID varchar(20),
    OrderDate datetime
);

-- Declare XML and handle
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(max);

-- Set XML data
SET @xmlDocument = N'
<ROOT>
  <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
    <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00" />
    <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00" />
  </Customers>
  <Customers CustomerID="XYZBB" ContactName="Steve" CompanyName="Company2">
    No Orders yet!
  </Customers>
</ROOT>';

-- Prepare the XML document
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;

-- Insert customers
INSERT INTO Customers
SELECT * FROM OPENXML(@docHandle, '/ROOT/Customers') WITH Customers;

-- Insert orders
INSERT INTO Orders
SELECT * FROM OPENXML(@docHandle, '//Orders') WITH Orders;

-- Query with OPENXML
SELECT * FROM OPENXML(@docHandle, '/ROOT/Customers/Orders')
  WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime);

-- Clean up the XML handle
EXEC sp_xml_removedocument @docHandle;

Example: Using nodes() Method

SELECT 
    nref.value('(first-name/text())[1]', 'nvarchar(50)') AS FirstName,
    nref.value('(last-name/text())[1]', 'nvarchar(50)') AS LastName
FROM T
CROSS APPLY xCol.nodes('//author') AS R(nref)
WHERE nref.exist('first-name[. != "David"]') = 1;

Looping Through XML Using OPENXML()

DECLARE name_cursor CURSOR FOR 
    SELECT xCol FROM T;

OPEN name_cursor;

DECLARE @xmlVal XML;
DECLARE @idoc int;

FETCH NEXT FROM name_cursor INTO @xmlVal;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlVal;

    SELECT * 
    FROM OPENXML(@idoc, '//author') 
    WITH (
        FirstName varchar(50) 'first-name',
        LastName varchar(50) 'last-name'
    ) R
    WHERE R.FirstName != 'David';

    EXEC sp_xml_removedocument @idoc;

    FETCH NEXT FROM name_cursor INTO @xmlVal;
END

CLOSE name_cursor;
DEALLOCATE name_cursor;

Example XML Structure

<Customers xmlns:xsd="http://www.w3.org/2001/XMLSchema"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <Customer>
       <ItemId>1001</ItemId>
       <Value>Mr Patel</Value>
   </Customer>
   <Customer>
      <ItemId>2002</ItemId>
      <Value>Mr Bhatt</Value>
   </Customer>
</Customers>

Extracting Data from XML

SELECT
    Cst.value('(ItemId)[1]', 'int') AS ItemID,
    Cst.value('(Value)[1]', 'Varchar(50)') AS CustomerName
FROM dbo.Sales.CustomerList.nodes('/Customers/Customer') AS A(Cst);

Using CROSS APPLY with XML

CREATE TABLE Sales2020 (ID int, CustomerList xml);

INSERT INTO Sales2020
SELECT 1, '
    <Customers xmlns:xsd="http://www.w3.org/2001/XMLSchema"
               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
       <Customer>
           <ItemId>1001</ItemId>
           <Value>Mr Patel</Value>
       </Customer>
       <Customer>
          <ItemId>2</ItemId>
          <Value>Mr Bhatt</Value>
       </Customer>
    </Customers>';

SELECT
   N.C.value('ItemId[1]', 'int') AS ItemId,
   N.C.value('Value[1]', 'varchar(100)') AS Value
FROM Sales2020
CROSS APPLY CustomerList.nodes('//Customer') AS N(C);

SQL Server help