Skip to content

Instantly share code, notes, and snippets.

@dveeden

dveeden/example_output.txt

Last active August 29, 2015 14:11
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save dveeden/0bdc8d89cecaa07a8698 to your computer and use it in GitHub Desktop.
Save dveeden/0bdc8d89cecaa07a8698 to your computer and use it in GitHub Desktop.
Download ZIP
MySQL XML to Key/Value
Raw
example_output.txt
mysql> source xml_xpath_kv.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.26 sec)
+-------------------------------------------------+------------------------------------------------------------------------------------------------------+
| key | value |
+-------------------------------------------------+------------------------------------------------------------------------------------------------------+
| APP_VERSION_CODE | 306020 |
| AVAILABLE_MEM_SIZE | 1296240640 |
| BUILD.BOARD | universal5420 |
| BUILD.BOOTLOADER | T700XXU1ANF9 |
| BUILD.BRAND | samsung |
| BUILD.CPU_ABI | armeabi-v7a |
| BUILD.CPU_ABI2 | armeabi |
| BUILD.DEVICE | klimtwifi |
| BUILD.DISPLAY | KOT49H.T700XXU1ANF9 |
| BUILD.FINGERPRINT | samsung/klimtwifixx/klimtwifi:4.4.2/KOT49H/T700XXU1ANF9:user/release-keys |
| BUILD.HARDWARE | universal5420 |
| BUILD.HOST | SWDD6012 |
| BUILD.HOUDINI_ABI | armeabi-v7a |
| BUILD.HOUDINI_ABI2 | armeabi |
| BUILD.ID | KOT49H |
| BUILD.IS_DEBUGGABLE | false |
| BUILD.IS_ENG_SECURED | false |
| BUILD.IS_SYSTEM_SECURE | false |
| BUILD.MANUFACTURER | samsung |
| BUILD.MODEL | SM-T700 |
| BUILD.PRODUCT | klimtwifixx |
| BUILD.RADIO | unknown |
| BUILD.SERIAL | 32045556c3bf31e9 |
| BUILD.TAGS | release-keys |
| BUILD.TIME | 1404130570000 |
| BUILD.TYPE | user |
| BUILD.UNKNOWN | unknown |
| BUILD.USER | dpi |
| CRASH_CONFIGURATION.arrange | 0 |
| CRASH_CONFIGURATION.compatScreenHeightDp | 502 |
| CRASH_CONFIGURATION.compatScreenWidthDp | 320 |
| CRASH_CONFIGURATION.compatSmallestScreenWidthDp | 320 |
| CRASH_CONFIGURATION.densityDpi | 320 |
| CRASH_CONFIGURATION.FlipFont | 1 |
| CRASH_CONFIGURATION.fontScale | 1.0 |
| CRASH_CONFIGURATION.hardKeyboardHidden | HARDKEYBOARDHIDDEN_YES |
| CRASH_CONFIGURATION.keyboard | KEYBOARD_NOKEYS |
| CRASH_CONFIGURATION.keyboardHidden | KEYBOARDHIDDEN_NO |
| CRASH_CONFIGURATION.locale | en_US |
| CRASH_CONFIGURATION.mcc | 0 |
| CRASH_CONFIGURATION.mnc | 0 |
| CRASH_CONFIGURATION.navigation | NAVIGATION_NONAV |
| CRASH_CONFIGURATION.navigationHidden | NAVIGATIONHIDDEN_YES |
| CRASH_CONFIGURATION.orientation | ORIENTATION_PORTRAIT |
| CRASH_CONFIGURATION.screenHeightDp | 1255 |
| CRASH_CONFIGURATION.screenLayout | SCREENLAYOUT_SIZE_XLARGE+SCREENLAYOUT_LONG_NO+SCREENLAYOUT_LAYOUTDIR_LTR |
| CRASH_CONFIGURATION.screenWidthDp | 800 |
| CRASH_CONFIGURATION.seq | 478 |
| CRASH_CONFIGURATION.smallestScreenWidthDp | 800 |
| CRASH_CONFIGURATION.touchscreen | TOUCHSCREEN_FINGER |
| CRASH_CONFIGURATION.uiMode | UI_MODE_TYPE_NORMAL+UI_MODE_NIGHT_NO |
| CRASH_CONFIGURATION.userSetLocale | true |
| CUSTOM_DATA | |
| DISPLAY.height | 2560 |
| DISPLAY.metrics.density | x2.0 |
| DISPLAY.metrics.heightPixels | 2560 |
| DISPLAY.metrics.scaledDensity | x2.0 |
| DISPLAY.metrics.widthPixels | 1600 |
| DISPLAY.metrics.xdpi | 359.646 |
| DISPLAY.metrics.ydpi | 361.244 |
| DISPLAY.pixelFormat | 1 |
| DISPLAY.refreshRate | 60.000004fps |
| DISPLAY.width | 1600 |
| DUMPSYS_MEMINFO | Permission Denial: can't dump meminfo from from pid=9999, uid=1010263 without permission android.per |
| IS_SILENT | |
| PRODUCT | klimtwifixx |
| USER_APP_START_DATE | 2014-11-13T03:21:49.000-05:00 |
| USER_COMMENT | |
| USER_EMAIL | N/A |
+-------------------------------------------------+------------------------------------------------------------------------------------------------------+
69 rows in set (0.01 sec)
Raw
mysql_xml_kv.sql
DROP TABLE IF EXISTS xmldata;
DROP TABLE IF EXISTS data;
CREATE TABLE `xmldata` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`xmldata` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `data` (
`key` varchar(100) NOT NULL,
`value` varchar(100) DEFAULT NULL,
PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO xmldata(xmldata) VALUES('<?xml version="1.0" encoding="UTF-8" standalone="yes"?><OtherData xmlns="http://log.xyz.com"><NVP><N>USER_EMAIL</N><V>N/A</V></NVP><NVP><N>USER_COMMENT</N><V></V></NVP><NVP><N>IS_SILENT</N><V></V></NVP><NVP><N>APP_VERSION_CODE</N><V>306020</V></NVP><NVP><N>CRASH_CONFIGURATION</N><NVP><N>FlipFont</N><V>1</V></NVP><NVP><N>arrange</N><V>0</V></NVP><NVP><N>compatScreenHeightDp</N><V>502</V></NVP><NVP><N>compatScreenWidthDp</N><V>320</V></NVP><NVP><N>compatSmallestScreenWidthDp</N><V>320</V></NVP><NVP><N>densityDpi</N><V>320</V></NVP><NVP><N>fontScale</N><V>1.0</V></NVP><NVP><N>hardKeyboardHidden</N><V>HARDKEYBOARDHIDDEN_YES</V></NVP><NVP><N>keyboard</N><V>KEYBOARD_NOKEYS</V></NVP><NVP><N>keyboardHidden</N><V>KEYBOARDHIDDEN_NO</V></NVP><NVP><N>locale</N><V>en_US</V></NVP><NVP><N>mcc</N><V>0</V></NVP><NVP><N>mnc</N><V>0</V></NVP><NVP><N>navigation</N><V>NAVIGATION_NONAV</V></NVP><NVP><N>navigationHidden</N><V>NAVIGATIONHIDDEN_YES</V></NVP><NVP><N>orientation</N><V>ORIENTATION_PORTRAIT</V></NVP><NVP><N>screenHeightDp</N><V>1255</V></NVP><NVP><N>screenLayout</N><V>SCREENLAYOUT_SIZE_XLARGE+SCREENLAYOUT_LONG_NO+SCREENLAYOUT_LAYOUTDIR_LTR</V></NVP><NVP><N>screenWidthDp</N><V>800</V></NVP><NVP><N>seq</N><V>478</V></NVP><NVP><N>smallestScreenWidthDp</N><V>800</V></NVP><NVP><N>touchscreen</N><V>TOUCHSCREEN_FINGER</V></NVP><NVP><N>uiMode</N><V>UI_MODE_TYPE_NORMAL+UI_MODE_NIGHT_NO</V></NVP><NVP><N>userSetLocale</N><V>true</V></NVP></NVP><NVP><N>DUMPSYS_MEMINFO</N><V>Permission Denial: can\'t dump meminfo from from pid=9999, uid=1010263 without permission android.permission.DUMP</V></NVP><NVP><N>BUILD</N><NVP><N>BOARD</N><V>universal5420</V></NVP><NVP><N>BOOTLOADER</N><V>T700XXU1ANF9</V></NVP><NVP><N>BRAND</N><V>samsung</V></NVP><NVP><N>CPU_ABI</N><V>armeabi-v7a</V></NVP><NVP><N>CPU_ABI2</N><V>armeabi</V></NVP><NVP><N>DEVICE</N><V>klimtwifi</V></NVP><NVP><N>DISPLAY</N><V>KOT49H.T700XXU1ANF9</V></NVP><NVP><N>FINGERPRINT</N><V>samsung/klimtwifixx/klimtwifi:4.4.2/KOT49H/T700XXU1ANF9:user/release-keys</V></NVP><NVP><N>HARDWARE</N><V>universal5420</V></NVP><NVP><N>HOST</N><V>SWDD6012</V></NVP><NVP><N>HOUDINI_ABI</N><V>armeabi-v7a</V></NVP><NVP><N>HOUDINI_ABI2</N><V>armeabi</V></NVP><NVP><N>ID</N><V>KOT49H</V></NVP><NVP><N>IS_DEBUGGABLE</N><V>false</V></NVP><NVP><N>IS_ENG_SECURED</N><V>false</V></NVP><NVP><N>IS_SYSTEM_SECURE</N><V>false</V></NVP><NVP><N>MANUFACTURER</N><V>samsung</V></NVP><NVP><N>MODEL</N><V>SM-T700</V></NVP><NVP><N>PRODUCT</N><V>klimtwifixx</V></NVP><NVP><N>RADIO</N><V>unknown</V></NVP><NVP><N>SERIAL</N><V>32045556c3bf31e9</V></NVP><NVP><N>TAGS</N><V>release-keys</V></NVP><NVP><N>TIME</N><V>1404130570000</V></NVP><NVP><N>TYPE</N><V>user</V></NVP><NVP><N>UNKNOWN</N><V>unknown</V></NVP><NVP><N>USER</N><V>dpi</V></NVP></NVP><NVP><N>PRODUCT</N><V>klimtwifixx</V></NVP><NVP><N>DISPLAY</N><NVP><N>width</N><V>1600</V></NVP><NVP><N>height</N><V>2560</V></NVP><NVP><N>pixelFormat</N><V>1</V></NVP><NVP><N>refreshRate</N><V>60.000004fps</V></NVP><NVP><N>metrics.density</N><V>x2.0</V></NVP><NVP><N>metrics.scaledDensity</N><V>x2.0</V></NVP><NVP><N>metrics.widthPixels</N><V>1600</V></NVP><NVP><N>metrics.heightPixels</N><V>2560</V></NVP><NVP><N>metrics.xdpi</N><V>359.646</V></NVP><NVP><N>metrics.ydpi</N><V>361.244</V></NVP></NVP><NVP><N>AVAILABLE_MEM_SIZE</N><V>1296240640</V></NVP><NVP><N>USER_APP_START_DATE</N><V>2014-11-13T03:21:49.000-05:00</V></NVP><NVP><N>CUSTOM_DATA</N></NVP></OtherData>');
DROP PROCEDURE IF EXISTS xmltotable;
delimiter /
CREATE PROCEDURE xmltotable ()
BEGIN
DECLARE kvcount INT;
DECLARE kvdone INT DEFAULT 0;
DECLARE topkey,topvalue VARCHAR(255);
DECLARE innerkvcount INT;
DECLARE innerkvdone INT DEFAULT 0;
SELECT extractValue(xmldata, 'count(/OtherData/NVP)')
FROM xmldata INTO kvcount;
-- SELECT CONCAT('kvcount: ',kvcount) AS info;
kvloop: LOOP
SET kvdone := kvdone + 1;
IF kvdone > kvcount THEN
LEAVE kvloop;
END IF;
SET @i := kvdone;
-- SELECT CONCAT('@i: ',@i) AS info;
SELECT extractValue(xmldata, concat('/OtherData/NVP[',@i,']/N'))
FROM xmldata INTO topkey;
SELECT extractValue(xmldata, concat('/OtherData/NVP[',@i,']/V'))
FROM xmldata INTO topvalue;
-- SELECT CONCAT('topkey: "',topkey,'"') AS info;
-- SELECT CONCAT('topvalue: "',topvalue,'"') AS info;
SELECT extractValue(xmldata, concat('count(/OtherData/NVP[',@i,']/NVP)'))
FROM xmldata INTO innerkvcount;
IF innerkvcount < 1 THEN
INSERT INTO data(`key`, value) VALUES (topkey, topvalue);
ELSE
SET innerkvdone := 0;
innerloop: LOOP
SET innerkvdone := innerkvdone + 1;
IF innerkvdone > innerkvcount THEN
LEAVE innerloop;
END IF;
SET @y := innerkvdone;
-- SELECT @i, @y;
-- SELECT
-- CONCAT(topkey,'.',
-- extractValue(xmldata,
-- concat('/OtherData/NVP[',@i,']/NVP[',@y,']/N'))) as `key`,
-- extractValue(xmldata,
-- concat('/OtherData/NVP[',@i,']/NVP[',@y,']/V')) as `value`
-- FROM xmldata;
INSERT INTO data(`key`, value)
SELECT
CONCAT(topkey,'.',
extractValue(xmldata,
concat('/OtherData/NVP[',@i,']/NVP[',@y,']/N'))) as `key`,
extractValue(xmldata,
concat('/OtherData/NVP[',@i,']/NVP[',@y,']/V')) as `value`
FROM xmldata;
END LOOP innerloop;
END IF;
END LOOP kvloop;
END;/
delimiter ;
CALL xmltotable();
SELECT * FROM data;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment