MySQL xpath ExtractValue with delimiter

Hello,

I recently noticed the XPATH option inside MySQL.

What a wonderfull way for handling XML. (http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html)

I used ExtractFunction, and noticed that this function is space delimited:

Because ExtractValue() returns multiple matches as a single space-delimited string

So I needed to create the same function with custom text delimted (comma, semi-colon or anything you want).

The function called: EXTRACTVALUE_ALL which is the same as EXTRACTVALUE just get a delimiter text to seperate between results. (seperated text).
Tested on MySQL 5.5.28.

 

Create Function CODE:

DROP FUNCTION IF EXISTS EXTRACTVALUE_ALL;


DELIMITER |
CREATE FUNCTION EXTRACTVALUE_ALL(p_xml TEXT, p_xpathExpr TEXT, p_delimiter TEXT) RETURNS TEXT
BEGIN
DECLARE total_elements INT;
DECLARE xpath_expression_count, xpath_expression_index  TEXT;
DECLARE single_tag, result  TEXT;

# calc TOTAL elements
SET xpath_expression_count = CONCAT('count(', p_xpathExpr, ')');
SELECT EXTRACTVALUE(p_xml, xpath_expression_count) INTO total_elements;

# run over elements (create long text)
SET result = '';
SET xpath_expression_index = CONCAT(p_xpathExpr, '[$@i]');
SET @i = 1;
WHILE @i <= total_elements DO
SET single_tag = EXTRACTVALUE(p_xml, xpath_expression_index);
SET result = IF(result='', single_tag, CONCAT(result, p_delimiter, single_tag));
SET @i = @i + 1;
END WHILE;

# return total result
RETURN result;
END |

Usage Code:

SET @XXXMMMMLLL = '  time1   time2 ';

SELECT EXTRACTVALUE_ALL(@XXXMMMMLLL, '/descendant-or-self::time', '  |  ');

Output:

time1  |  time2

Rememeber that you will need a MYSQL permission for creating new functions.

MDB-BLOG :)

 

Working with sql xml | Sql server

Here is a code for manipulating XML object under SQL Server.
This code allow to recieve attribute and note contents.
Best for xml manipulations.

--Under this configuration:

DECLARE @xml XML
SET @xml = '<root><a><b id="456"></b><c>world...</c></a></root>'

--Get attribute's content from xml field:

SELECT @xml.value('(//root/a/b/@id)[1]','int') AS 'Attribute Content'

--Get node's content from xml field:

SELECT @xml.value('(//root/a/c)[1]','nvarchar(MAX)') AS 'Node Content'

Sql Server | Check if exist: DataBase, Table or Stored-Procedure

Here is a code for checking if an object exist in SQL SERVER.
This may be helpful if you have 2 enviroments and on each one of them
the object (db, table, stored-procedure) are diffrent,
so you can check whether this is the specific db and perform actions...

Check is Database exists:

if db_id('dbname') is not null

Check is Table exists:

if object_id('object_name', 'U') is not null -- for table

Check is Store-Procedure exists:

if object_id('object_name', 'P') is not null -- for SP

hope this is helps...

Sql Server | Compare 2 tables columns

Here is a sql statement for comapring two tables (may be in diffrent DBs) columns.
This SQL statement compare only if column exist in both or not and return the difference.
You may improve the sql statement for comapring more... :)
I hope this sql script will be helpful...

DECLARE @Db1 NVARCHAR(MAX)
DECLARE @Table1 NVARCHAR(MAX)
DECLARE @Db2 NVARCHAR(MAX)
DECLARE @Table2 NVARCHAR(MAX)
DECLARE @Sql NVARCHAR(MAX)

SET @Db1 = 'MMIS_SNAP'
SET @Table1 = 'CodeTablesData'
SET @Db2 = 'MMIS_SNAP'
SET @Table2 = 'CodeTables'
SET @Sql = ' ' +
' SELECT ''in ' + @Db1 + '.' + @Table1 + ' --- not in ' + @Db2 + '.' + @Table2 + ''' AS TITLE, a.TABLE_CATALOG, a.column_name ' +
' FROM ' + @Db1 + '.INFORMATION_SCHEMA.COLUMNS a ' +
' WHERE a.column_name NOT IN (SELECT column_name ' +
' FROM ' + @Db2 + '.INFORMATION_SCHEMA.COLUMNS b ' +
' WHERE b.table_name IN (''' + @Table2 + ''')) ' +
' AND a.table_name IN (''' + @Table1 + ''') ' +

' UNION ALL ' +

' SELECT ''in ' + @Db2 + '.' + @Table2 + ' --- not in ' + @Db1 + '.' + @Table1 + ''' AS TITLE, a.TABLE_CATALOG, a.column_name ' +
' FROM ' + @Db2 + '.INFORMATION_SCHEMA.COLUMNS a ' +
' WHERE a.column_name NOT IN (SELECT column_name ' +
' FROM ' + @Db1 + '.INFORMATION_SCHEMA.COLUMNS b ' +
' WHERE b.table_name IN (''' + @Table1 + ''')) ' +
' AND a.table_name IN (''' + @Table2 + ''') ' +
''

EXEC (@Sql)

You may create store-procedure from this script, and the automaticly run it
on all your tables.
This may be automatic table columns comparison...


post your additional information as comments, thanks


ANOTHER SAMPLE, faster compare with 'Allow null' & 'Data type':

SET @Db1 = 'TestDB'
SET @Table1 = 'Users'
SET @Db2 = 'TestDB'
SET @Table2 = 'Users2'

SET @Sql = ' ' +
' SELECT ''in ' + @Db1 + '.' + @Table1 + ' --- not in ' + @Db2 + '.' + @Table2 + ''' AS TITLE, ' +
' a.TABLE_CATALOG, ' +
' a.COLUMN_NAME, ' +
' a.IS_NULLABLE, ' +
' a.DATA_TYPE ' +
' FROM ' + @Db1 + '.INFORMATION_SCHEMA.COLUMNS a ' +
' WHERE NOT EXISTS ( ' +
' SELECT b.COLUMN_NAME ' +
' FROM ' + @Db2 + '.INFORMATION_SCHEMA.COLUMNS b ' +
' WHERE b.table_name IN (''' + @Table2 + ''') ' +
' AND b.COLUMN_NAME = a.COLUMN_NAME ' +
' AND b.IS_NULLABLE = a.IS_NULLABLE ' +
' AND b.DATA_TYPE = a.DATA_TYPE ' +
' ) ' +
' AND a.table_name IN (''' + @Table1 + ''') ' +
' ' +
' UNION ALL ' +
' ' +
' SELECT ''in ' + @Db2 + '.' + @Table2 + ' --- not in ' + @Db1 + '.' + @Table1 + ''' AS TITLE, ' +
' a.TABLE_CATALOG, ' +
' a.COLUMN_NAME, ' +
' a.IS_NULLABLE, ' +
' a.DATA_TYPE ' +
' FROM ' + @Db2 + '.INFORMATION_SCHEMA.COLUMNS a ' +
' WHERE NOT EXISTS ( ' +
' SELECT b.COLUMN_NAME ' +
' FROM ' + @Db1 + '.INFORMATION_SCHEMA.COLUMNS b ' +
' WHERE b.table_name IN (''' + @Table1 + ''') ' +
' AND b.COLUMN_NAME = a.COLUMN_NAME ' +
' AND b.IS_NULLABLE = a.IS_NULLABLE ' +
' AND b.DATA_TYPE = a.DATA_TYPE ' +
' ) ' +
' AND a.table_name IN (''' + @Table2 + ''') ' +
' '

PRINT @Sql
EXEC (@Sql)