extract value from xml in mysql

Question

i am trying to extract values from xml. i am getting problem when xml has attribute. like following Stored Procedure

DELIMITER $$
DROP PROCEDURE IF EXISTS `excel`.`insert_items` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_items`()
BEGIN
SET @xml = '<items><item>
        <value columntype="0">Single Line Text_01</value>
    <value columntype="1">Single Line Text_12341</value>
    <value columntype="2">Single Line Text_21</value>
    <value columntype="3">Single Line Text_31</value>
    <value columntype="4">Single Line Text_41</value>
    </item>
</items>';
SELECT @columntype, ExtractValue(@xml, 'items/item/value[items/item/value/@columntype=0]');    
END $$
DELIMITER ;

What is the problem here on extracting ?

thar45
– thar45

2013-05-10 06:30:47 +00:00
Commented May 10, 2013 at 6:30 — thar45
– thar45, Commented May 10, 2013 at 6:30

peterm · Accepted Answer · 2013-05-10 06:44:16Z

11

To get a value of an element with attribute columntype="0"

SELECT ExtractValue(@xml, 'items/item/value[@columntype=0]') value;

Output:

|               VALUE |
-----------------------
| Single Line Text_01 |

SQLFiddle

edited May 10, 2013 at 6:44

answered May 10, 2013 at 6:31

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ali Over a year ago

What if I have this XML:

<items> 	<item> 	    <value1>Single Line Text_01</value> 	    <value2>Single Line Text_12341</value> 	    <value3>Single Line Text_21</value> 	    <value4>Single Line Text_31</value> 	    <value5>Single Line Text_41</value>     </item> </items>

How can I extract values individually in this scenario?

2787184 · Accepted Answer · 2014-12-12 14:26:58Z

0

Following code will print value of columntype and value :

 DELIMITER $$
    DROP PROCEDURE IF EXISTS `TEST`$$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `TEST`(IN XMLINPUT TEXT)
    BEGIN
    DECLARE VALUECOUNT INT UNSIGNED;
    DECLARE VALUE INT UNSIGNED;
    DECLARE I INT UNSIGNED DEFAULT 1;
    SET VALUECOUNT:=EXTRACTVALUE(XMLINPUT, 'COUNT(/items/item/value)');
    SELECT VALUECOUNT; -- print 5
    WHILE (I <= VALUECOUNT) DO 
        SELECT EXTRACTVALUE(XMLINPUT, '/items/item/value[$I]/@columntype[1]'); -- will print columntype tag
        SELECT EXTRACTVALUE(XMLINPUT, '/items/item/value[$I]'); -- will print value tag     
    SET I:= I+1;
    END WHILE;

    END$$DELIMITER ;

edited Dec 12, 2014 at 14:26

answered Dec 12, 2014 at 14:18

2787184

3,92110 gold badges54 silver badges85 bronze badges

Collectives™ on Stack Overflow

extract value from xml in mysql

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related