CODEWITHSUNDEEP
mysqlsql
Yashwanth Aluru
Yashwanth Aluru

Reputation: 1193

Using ExtractValue( ) function to insert XML data in a table in MySQL

I've a STORED PROCEDURE in which I've a table as follows:

CREATE TEMPORARY TABLE tt_ASSET_DETAILS

 (
      SiteGroup VARCHAR(30),
      SiteNumber VARCHAR(50),
      AssetCategory VARCHAR(30),
      Address VARCHAR(50),
      AssetName VARCHAR(50)
   );

I want to insert data into this table from the following XML:

<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>

Now, I want the data between the node set <Asset> </Asset> to be inserted as first row and the other set of <Asset> </Asset> as second row.

For that I used the following code which is inserting both the sets of <Asset> </Asset> in a single row of the table,

INSERT INTO tt_ASSET_DETAILS(SiteGroup,SiteNumber,AssetCategory,Address,AssetName)
   SELECT SiteGroup,SiteNumber,AssetCategory,Address,AssetNumber
   FROM
    (select ExtractValue(AssetsXML,'//ManageAsset/Asset/SiteGroup') as SiteGroup,
            ExtractValue(AssetsXML,'//ManageAsset/Asset/SiteNumber') as SiteNumber,
            ExtractValue(AssetsXML,'//ManageAsset/Asset/AssetCategory') as AssetCategory,
            ExtractValue(AssetsXML,'//ManageAsset/Asset/Address') as Address,
            ExtractValue(AssetsXML,'//ManageAsset/Asset/AssetNumber') as AssetNumber) XM;

Someone, Please help me in correcting my code so that my required result can be achieved!!!

Upvotes: 0

Views: 1129

Answers (1)

Yashwanth Aluru
Yashwanth Aluru

Reputation: 1193

I've got the answer to my question. using WHILE loop serves the need!

    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;

Now if I query the data from the table as SELECT * FROM tt_ASSET_DETAILS; I'll get the data inserted into the table in different rows!

Upvotes: 2

Related Questions