查询 json 列并在 XML 中导出数据-解网

问：

目前正在处理一个报告，以导出发送到 API 的一些值。数据库是 Postgresql，并且有一个列存储此数据。

下面是一个示例：

{
  "url": "https://www.z.com/psd/pmzservice.asmx",
  "headers": {
    "content-type": "application/xml"
  },
  "body": "<soap:Envelope xmlns:soap=\"http://www.a.org/05/soap-envelope\" xmlns:pvs=\"http://www.z.com/WebServices/PMZService/\">\r\n <soap:Header/>\r\n <soap:Body>\r\n <pvs:ValidateTinName>\r\n <pvs:TinName>\r\n <pvs:TIIN>83163943234</pvs:TIIN>\r\n <pvs:LName></pvs:LName>\r\n <pvs:FName>Dedetizadora Johnsnville Ltda</pvs:FName>\r\n <pvs:Encryption></pvs:Encryption>\r\n <pvs:Giin></pvs:Giin>\r\n </pvs:TinName>\r\n <pvs:CurUser>\r\n <pvs:UserID></pvs:UserID>",
  "method": "POST",
  "field_id": 10896231321,
  "timeout": 65
}

这是我第一次处理 XML，并且基于我一直在搜索的内容。我使用 XPath 函数进行了此查询以获取 FName 中的值

select  xpath(
            '/soap:Envelope/soap:Body/pvs:ValidateTiinName/pvs:TinName/pvs:FName/text()',
            (a.json_column::json->>'body')::xml,
            ARRAY[
              ('soap', 'http://www.a.org/05/soap-envelope')::xml,
              ('pvs', 'http://www.z.com/WebServices/PMZService')::xml
            ]::xml[]
          )::text AS fname_value
from table a

但是当我运行它时，我面临以下问题： 错误：无法将类型记录转换为xml

您能否帮助我了解如何导出存储在 FName 和 TIIN 中的数据？提前致谢！

JSON XML PostgreSQL

源的列数据类型是什么？并且 JSON 正文中的 XML 内容有几个 XML 错误（它“格式不正确！这个XML是你可以修复的吗？<pvs：CurUser> 开始标记没有相应的结束标记。XML 内容过早结束。缺少 <pvs：ValidateTinName>、<soap：Body> 和 <soap：Envelope> 的结束标记。

答：

0赞 Adrian Maxwell 11/15/2023 #1

您遇到的问题包括嵌入的 XML 格式不正确的问题，因此，虽然您可以使用 json finctions 来获取 body 元素，但其上的任何 xml 函数都将失败。pvs：CurUser 开始标记没有相应的结束标记。XML 内容过早结束。缺少 pvs：ValidateTinName、soap：Body 和 soap：Envelope 的结束标记。

下面尝试确定问题所在，请注意，第二个插入包含对正文 xml 部分的修复。然后，在最后一个查询中，一旦 xml 格式正确，就可以获取 TIIN 和 FName：

CREATE TABLE mytable (
  id serial PRIMARY KEY,
  json_value jsonb
);

CREATE TABLE

INSERT INTO mytable (json_value)
VALUES
  ('{
    "url": "https://www.z.com/psd/pmzservice.asmx",
    "headers": {
      "content-type": "application/xml"
    },
    "body": "<soap:Envelope xmlns:soap=\"http://www.a.org/05/soap-envelope\" xmlns:pvs=\"http://www.z.com/WebServices/PMZService/\">\r\n <soap:Header/>\r\n <soap:Body>\r\n <pvs:ValidateTinName>\r\n <pvs:TinName>\r\n <pvs:TIIN>83163943234</pvs:TIIN>\r\n <pvs:LName></pvs:LName>\r\n <pvs:FName>Dedetizadora Johnsnville Ltda</pvs:FName>\r\n <pvs:Encryption></pvs:Encryption>\r\n <pvs:Giin></pvs:Giin>\r\n </pvs:TinName>\r\n <pvs:CurUser>\r\n <pvs:UserID></pvs:UserID>",
    "method": "POST",
    "field_id": 10896231321,
    "timeout": 65
  }');

INSERT 0 1

SELECT 
  id, 
  (json_value ->> 'body') body
FROM 
  mytable;

编号	身体
1	<soap：信封 xmlns：soap=“http://www.a.org/05/soap-envelope” xmlns：pvs=“http://www.z.com/WebServices/PMZService/”> <soap：Header/> <soap：Body> <pvs：ValidateTinName> <pvs：TinName> <pvs：TIIN>83163943234</pvs：TIIN> <pvs：LName></pvs：LName> <pvs：FName>Dedetizadora Johnsnville Ltda</pvs：FName> <pvs：Encryption></pvs：Encryption> <pvs：Giin></pvs：Giin> </pvs：TinName> <pvs：CurUser> <pvs：UserID></pvs：UserID>

编号

身体

1

<soap：信封 xmlns：soap=“http://www.a.org/05/soap-envelope” xmlns：pvs=“http://www.z.com/WebServices/PMZService/”> <soap：Header/> <soap：Body> <pvs：ValidateTinName> <pvs：TinName> <pvs：TIIN>83163943234</pvs：TIIN> <pvs：LName></pvs：LName> <pvs：FName>Dedetizadora Johnsnville Ltda</pvs：FName>

<pvs：Encryption></pvs：Encryption> <pvs：Giin></pvs：Giin> </pvs：TinName> <pvs：CurUser>

<pvs：UserID></pvs：UserID>

SELECT 1

SELECT 
  (xpath('/soap:Envelope/soap:Body/pvs:ValidateTinName/pvs:TinName/pvs:FName/text()', XMLPARSE(CONTENT json_value ->> 'body'), ARRAY[ARRAY['soap', 'http://www.a.org/05/soap-envelope'], ARRAY['pvs', 'http://www.z.com/WebServices/PMZService/']]::TEXT[]))[1]::TEXT AS fname,
  (xpath('/soap:Envelope/soap:Body/pvs:ValidateTinName/pvs:TinName/pvs:TIIN/text()', XMLPARSE(CONTENT json_value ->> 'body'), ARRAY[ARRAY['soap', 'http://www.a.org/05/soap-envelope'], ARRAY['pvs', 'http://www.z.com/WebServices/PMZService/']]::TEXT[]))[1]::TEXT AS tiin
FROM 
  mytable;

ERROR:  invalid XML content
DETAIL:  line 13: Premature end of data in tag CurUser line 12
 <pvs:UserID></pvs:UserID>
                          ^
line 13: chunk is not well balanced
 <pvs:UserID></pvs:UserID>
                          ^

INSERT INTO mytable (json_value)
VALUES
 ('{
    "url": "https://www.z.com/psd/pmzservice.asmx",
    "headers": {
      "content-type": "application/xml"
    },
    "body": "<soap:Envelope xmlns:soap=\"http://www.a.org/05/soap-envelope\" xmlns:pvs=\"http://www.z.com/WebServices/PMZService/\">\n<soap:Header/>\n<soap:Body>\n<pvs:ValidateTinName>\n<pvs:TinName>\n<pvs:TIIN>83163943234</pvs:TIIN>\n<pvs:LName></pvs:LName>\n<pvs:FName>Dedetizadora Johnsnville Ltda</pvs:FName>\n<pvs:Encryption></pvs:Encryption>\n<pvs:Giin></pvs:Giin>\n</pvs:TinName>\n<pvs:CurUser>\n<pvs:UserID></pvs:UserID></pvs:CurUser>\n</pvs:ValidateTinName>\n</soap:Body>\n</soap:Envelope>\n",
    "method": "POST",
    "field_id": 10896231321,
    "timeout": 65
  }');

INSERT 0 1

SELECT 
  id, 
  (json_value ->> 'body') body
FROM 
  mytable;

编号	身体
1	<soap：信封 xmlns：soap=“http://www.a.org/05/soap-envelope” xmlns：pvs=“http://www.z.com/WebServices/PMZService/”> <soap：Header/> <soap：Body> <pvs：ValidateTinName> <pvs：TinName> <pvs：TIIN>83163943234</pvs：TIIN> <pvs：LName></pvs：LName> <pvs：FName>Dedetizadora Johnsnville Ltda</pvs：FName> <pvs：Encryption></pvs：Encryption> <pvs：Giin></pvs：Giin> </pvs：TinName> <pvs：CurUser> <pvs：UserID></pvs：UserID>
2	<soap：信封 xmlns：soap=“http://www.a.org/05/soap-envelope” xmlns：pvs=“http://www.z.com/WebServices/PMZService/”><soap：Header/><soap：Body><pvs：ValidateTinName><pvs：TinName><pvs：TIIN>83163943234</pvs：TIIN><pvs：LName></pvs：LName> <pvs：FName>Dedetizadora Johnsnville Ltda</pvs：FName> <pvs：加密></pvs：Encryption><pvs：Giin></pvs：Giin></pvs：TinName><pvs：CurUser><pvs：UserID></pvs：UserID></pvs：CurUser></pvs：ValidateTinName></soap：Body></soap：Envelope>

编号

身体

1

<soap：信封 xmlns：soap=“http://www.a.org/05/soap-envelope” xmlns：pvs=“http://www.z.com/WebServices/PMZService/”> <soap：Header/> <soap：Body> <pvs：ValidateTinName> <pvs：TinName> <pvs：TIIN>83163943234</pvs：TIIN> <pvs：LName></pvs：LName> <pvs：FName>Dedetizadora Johnsnville Ltda</pvs：FName>

<pvs：Encryption></pvs：Encryption> <pvs：Giin></pvs：Giin> </pvs：TinName> <pvs：CurUser>

<pvs：UserID></pvs：UserID>

2

<soap：信封 xmlns：soap=“http://www.a.org/05/soap-envelope” xmlns：pvs=“http://www.z.com/WebServices/PMZService/”><soap：Header/><soap：Body><pvs：ValidateTinName><pvs：TinName><pvs：TIIN>83163943234</pvs：TIIN><pvs：LName></pvs：LName>
<pvs：FName>Dedetizadora Johnsnville Ltda</pvs：FName>

<pvs：
加密></pvs：Encryption><pvs：Giin></pvs：Giin></pvs：TinName><pvs：CurUser><pvs：UserID></pvs：UserID></pvs：CurUser></pvs：ValidateTinName></soap：Body></soap：Envelope>

SELECT 2

SELECT 
  id,
  (xpath('/soap:Envelope/soap:Body/pvs:ValidateTinName/pvs:TinName/pvs:FName/text()', XMLPARSE(CONTENT json_value ->> 'body'), ARRAY[ARRAY['soap', 'http://www.a.org/05/soap-envelope'], ARRAY['pvs', 'http://www.z.com/WebServices/PMZService/']]::TEXT[]))[1]::TEXT AS fname,
  (xpath('/soap:Envelope/soap:Body/pvs:ValidateTinName/pvs:TinName/pvs:TIIN/text()', XMLPARSE(CONTENT json_value ->> 'body'), ARRAY[ARRAY['soap', 'http://www.a.org/05/soap-envelope'], ARRAY['pvs', 'http://www.z.com/WebServices/PMZService/']]::TEXT[]))[1]::TEXT AS tiin
FROM 
  mytable
where id = 2

编号	fname	蒂因
2	Dedetizadora Johnsnville Ltda	83163943234

SELECT 1

小提琴

上一个：使用或覆盖已弃用的 API。注意：使用 -Xlint：deprecation 重新编译以获取详细信息

下一个：TextView.text 在 TableLayout TableRow 内被剪切为中间宽度

查询 json 列并在 XML 中导出数据

Querying a json column and export data inside a XML

评论