如何在Postgres中将XML转换为JSONB [英] How to convert a XML into JSONB within Postgres
本文介绍了如何在Postgres中将XML转换为JSONB的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个表 Table1 ，其中包含 XML数据类型列， ColumnA 和另一个JSONB列 ColumnB
I have a table, Table1 that contains a XML data type column, ColumnA and another JSONB column ColumnB
如何将 ColumnA 中的数据转换为 ColumnB ，大概使用SQL UPDATE 语句。在Postgres中是否有内置函数可以做到这一点？
How do I convert the data from ColumnA into ColumnB, presumably using an SQL UPDATE statement. Is there a built-in function in Postgres that does this?
推荐答案
据我所知，没有现成的函数
There is no out-of-box functions as I know.
尝试：
create or replace function xml_to_json(p_xml xml) returns jsonb as $$
declare
result json;
root text;
childs jsonb;
attr jsonb;
txt text;
begin
-- Get root element name
select (xpath('name(/*)', p_xml))[1]::text into root;
-- Process child nodes
select json_agg(xml_to_json(z))
from unnest(xpath('/'||root||'/*', p_xml)) with ordinality as c(z,i)
into childs;
-- Read attributes
select jsonb_agg(jsonb_build_object((xpath('name(/'||root||'/@*['||i||'])', p_xml))[1]::text, v))
from unnest(xpath('/'||root||'/@*', p_xml)::text[]) with ordinality as a(v,i)
into attr;
-- Read text
select (xpath('/'||root||'/text()', p_xml))[1]::text into txt;
-- Build object
result := jsonb_build_object(root, jsonb_build_object('attr', attr, 'text', txt, 'childs', childs));
return result;
end $$ language plpgsql immutable;
测试：
with t(x) as (values
('<root bar="foo" name="test" foo="bar">
<aaa bbb="111">
foo
bar
</aaa>
<bbb>
<ccc>222</ccc>
</bbb>
</root>'::xml),
('<?xml version="1.0" encoding="UTF-8"?>
<bookstore>
<book>
<title lang="en">Harry Potter</title>
<price>29.99</price>
</book>
<book>
<title lang="en">Learning XML</title>
<price>39.95</price>
</book>
</bookstore>'))
select jsonb_pretty(xml_to_json(x)) from t;
然后输出：
jsonb_pretty
---------------------------------------------------------
{ +
"root": { +
"attr": [ +
{ +
"bar": "foo" +
}, +
{ +
"name": "test" +
}, +
{ +
"foo": "bar" +
} +
], +
"text": "\n ", +
"childs": [ +
{ +
"aaa": { +
"attr": [ +
{ +
"bbb": "111" +
} +
], +
"text": "\n foo\n bar\n ", +
"childs": null +
} +
}, +
{ +
"bbb": { +
"attr": null, +
"text": "\n ", +
"childs": [ +
{ +
"ccc": { +
"attr": null, +
"text": "222", +
"childs": null +
} +
} +
] +
} +
} +
] +
} +
}
{ +
"bookstore": { +
"attr": null, +
"text": "\n ", +
"childs": [ +
{ +
"book": { +
"attr": null, +
"text": "\n ", +
"childs": [ +
{ +
"title": { +
"attr": [ +
{ +
"lang": "en" +
} +
], +
"text": "Harry Potter",+
"childs": null +
} +
}, +
{ +
"price": { +
"attr": null, +
"text": "29.99", +
"childs": null +
} +
} +
] +
} +
}, +
{ +
"book": { +
"attr": null, +
"text": "\n ", +
"childs": [ +
{ +
"title": { +
"attr": [ +
{ +
"lang": "en" +
} +
], +
"text": "Learning XML",+
"childs": null +
} +
}, +
{ +
"price": { +
"attr": null, +
"text": "39.95", +
"childs": null +
} +
} +
] +
} +
} +
] +
} +
}
(2 rows)
这篇关于如何在Postgres中将XML转换为JSONB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
