MySQL - Extract Data from an XML into a Table in a Stored Procedure

As a part of my work, I'm working on MySQL. When compared to SQL Server, it doesn't have many features and lacks in security too. Recently I've undergone a lot of pressure regarding a feature in MySQL i.e., XML in a Stored Procedure. In SQL Server, we have a data type with the name XML and it can be passed as input easily with the help of sp_xml_preparedocument, values can be extracted from it using OpenXML( ). But MySQL doesn't offer such System Stored Procedures or Data Type or Functions. It offers two built-in functions ExtractValue( ) and UpdateXML( ) to perform XML operations inside a Stored Procedure. These functions work even outside the Stored Procedure but if XML has to be used inside it, these two are the provided functions. In MySQL, there is no specific datatype for XML. It has to be passed into a VARCHAR field or a special field LONGTEXT which supports many number of characters than VARCHAR. In this article I'll show how to use XML inside a Stored Procedure using the above specified functions.

Let's create a Stored Procedure which takes an XML as input. It has a temporary table created. XML has to be passed so that the values of XML are stored in the table and display the result. To do this, let's create the stored procedure:

USE SAKILA;
delimiter //
CREATE PROCEDURE SAMPLE_PROC(AssetsXML LONGTEXT)
BEGIN
    CREATE TEMPORARY TABLE tt_ASSET_DETAILS
    (
      SiteGroup VARCHAR(30),
      SiteNumber VARCHAR(50),
      AssetCategory VARCHAR(30),
      Address VARCHAR(50),
      AssetName VARCHAR(50)
    );

    SET @COUNT = (SELECT EXTRACTVALUE(AssetsXML,'COUNT(/ManageAsset/Asset)'));
    SET @I = 1;
  
    WHILE(@I <= @COUNT) DO
  
        INSERT INTO tt_ASSET_DETAILS(SiteGroup,SiteNumber,AssetCategory,Address,AssetName)
        SELECT  ExtractValue(AssetsXML,CONCAT('/ManageAsset/Asset[',@I,']/SiteGroup')),
                ExtractValue(AssetsXML,CONCAT('/ManageAsset/Asset[',@I,']/SiteNumber')),
                ExtractValue(AssetsXML,CONCAT('/ManageAsset/Asset[',@I,']/AssetCategory')),
                ExtractValue(AssetsXML,CONCAT('/ManageAsset/Asset[',@I,']/Address')),
                ExtractValue(AssetsXML,CONCAT('/ManageAsset/Asset[',@I,']/AssetNumber'));
      
        SET @I = @I + 1;
  
    END WHILE;

    SELECT * FROM tt_ASSET_DETAILS;

END //

In the above Stored Procedure, a variable @COUNT is taken to count nodes inside the node <Asset> which is present inside <ManageAsset> node. The part COUNT(/ManageAsset/Asset) returns the number of nodes present inside the given xpath. The function ExtractValue( ) used in the INSERT statement extracts the value of the nodes present inside <Asset> and inserts their values into the specified columns of the table. As XML may have many number of such nodes a counter @I has been taken to insert values of nodes present in between <Asset> </Asset> as individual rows in the table. This counter variable gets incremented with while loop until its value becomes equal to the number of nodes existing in the XML.

After creating the stored procedure, execute it by passing the following XML as input. As I said earlier, XML has to be passed as either Varchar or Longtext field.

CALL SAMPLE_PROC('
 <ManageAsset>
  <Asset>
    <SiteGroup>Chicago</SiteGroup>
    <SiteNumber>4524</SiteNumber>
    <AssetCategory>Passenger Cars</AssetCategory>
    <Address>Address 1</Address>
    <AssetNumber>Tango</AssetNumber>
  </Asset>
  <Asset>
    <SiteGroup>Cincinnati</SiteGroup>
    <SiteNumber>5360</SiteNumber>
    <AssetCategory>Passenger Cars</AssetCategory>
    <Address>Address 2</Address>
    <AssetNumber>Mango</AssetNumber>
  </Asset>
</ManageAsset>');

After executing the procedure, you can observe the result. XML is stored as two different rows in the table.

 

The following blog post helped me a lot in acquiring my desired functionality,

http://rpbouman.blogspot.in/2006/03/importing-xml-data-into-mysql-using.html

A very sincere thanks to Roland Bouman for his educative post!