Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create or replace function xml2json (xml) returns text
- language plperlu as
- $func$
- use XML::XML2JSON;
- use strict;
- my $xml = shift;
- my $XML2JSON = XML::XML2JSON->new(pretty => 0); # 1 for readable json
- # work around horrid XML2JSON bug with elements consisting for <foo>0/foo>
- $xml =~ s!<([^>]+)>0</\1>!<$1>9999999999999999</$1>!g;
- # work around another horrid bug, which turns a string of all blanks into {}
- $xml =~ s!<([^>]+)>(\s*)</\1>!<$1>$2,Mxy;zptlk~</$1>!g;
- my $json = $XML2JSON->convert($xml);
- $json =~ s/9999999999999999/0/g;
- $json =~ s/,Mxy;zptlk~//g;
- # flatten the structure
- # nulls:
- $json =~ s/\{[^"]*"\@xsi:nil" ?: ?"true"[^"}]*}(,?)/null$1/gs;
- # numbers (not in scientific notation):
- # don't dequote a number with a leading zero if it's followed by
- # another digit - e.g. a zip code
- $json =~ s/{[^"]*"\$t" ?: ?"(-?(0|([1-9]\d*))(\.\d+)?)"[^"}]*}(,?)/$1$5/gs;
- # booleans:
- $json =~ s/{[^"]*"\$t" ?: ?"(true|false)"[^"}]*}(,?)/$1$2/gs;
- # strings:
- # arrays and composites are not decomposed, they just come in
- # as strings
- $json =~ s/{[^"]*"\$t" ?: ?"(([^"]|\\")*)"[^"}]*}(,?)/"$1"$3/gs;
- #remove wrapper
- # single row - make it an array of one
- $json =~ s/^.*?"row"\s*:\s*\{/\[\{/;
- $json =~ s/\}\s*}\s*}\s*$/\}\]/;
- # array of rows
- $json =~ s/^.*?"row"\s*:\s*\[/\[/;
- $json =~ s/\]\s*}\s*}\s*$/\]/;
- return $json;
- $func$;
- create or replace function query_to_json(query text) returns text
- language sql as
- $func$
- select xml2json(query_to_xml($1,true,false,''))
- $func$;
Add Comment
Please, Sign In to add comment