mysql xml字符串转数组_在mysql或sas中提取xml数组值

data work.parsed;

infile cards;

input;

length line_str $32000 rec_str $800 number type state country tx zip msa $100 elemname $32;

line_str = compress(_infile_, '"'); /* remove quotes */

line_str = translate(line_str, ':', ','); /* make : a key:value separator */

keep id number type state country tx zip msa;

id = _N_;

rec_count=countc(line_str, '{');

array elem {*} $ number type state country tx zip msa;/* order is important */

put rec_count=;

do r=1 to rec_count;

if r = 1 then rec_start=3;

else rec_start = rec_end + 4;

rec_end = findc(line_str, '}', rec_start) - 1;

rec_str=substr(line_str, rec_start, rec_end - rec_start + 1);

do i=1 to dim(elem);

elemname = vname(elem(i));

elem(i)= scan(rec_str, i * 2, ':');/* this way relying on all elements provided in record in expected order */

if findc(elem(i), '}') > 0 then elem(i) = substr(elem(i), 1, findc(elem(i), '}') - 1);

end;

output;

end;

cards;

[{"number":"8457215152","type":"Cell","state":"LA","country":"US","tz":"CT","zip":"70546","msa":"0"},{"number":"4363685555","type":"Cell","state":"LA","country":"US","tz":"CT","zip":"70546","msa":"2"},{"number":"33333","type":"Cell","state":"CA","country":"US","tz":"CT","zip":"33333","msa":"3"}]

;

run;当然，这对数据看起来有一些假设。

HTH

Vasja