在PostgreSQL中提取xml标签的值 [英] Extracting value of xml tag in PostgreSQL

问题描述

下面是我的Postgres表的列响应。我想从我的Postgres数据库中的所有行提取状态。状态可以是不同的大小，如 SUCCESS ，以及所以我不想使用substring函数。有办法吗？

 <？xml version =1.0？>< response> status> ERROR_MISSING_DATA< / status>< responseType> COUNTRY_MISSING< / responseType>< country_info> USA< / country_info>< phone_country_code> 1234< / phone_country_code>< / response> 
  

所以我的表结构是这样的

  Column |类型|修饰符
 
 ------------- + --------------------------- -  + ----------------------------------------------- ----------- 
 
 id | bigint | not null默认值nextval（'events_id_seq':: regclass）
 hostname |文本| not null 
 time |没有时区的时间戳| not null 
 trn_type |文本| 
 db_ret_code |文本| 
 request |文本| 
 response |文本| 
 wait_time |文本| 
  

我想从每个请求中提取状态。我该如何做？

下面是一个示例行。并假设表名为abc_events

  id | 1870667 
 hostname | abcd.local 
 time | 2013-04-16 00：00：23.861 
 trn_type | A 
请求| < / country_info>< phone_country_code> 1234< / country_info>< / country_info>< / country_info>< / country_info>< / country_info>< phone_country_code>< / response> 
 response | < / country_info>< phone_country_code> 1234< / country_info>< / country_info>< / country_info>< / country_info>< / country_info>< phone_country_code>< / response> 
  

解决方案

使用xpath() function：

  WITH x（col）AS（SELECT'<？xml version =1.0？& ; response>< status> ERROR_MISSING_DATA< / status>< / response>':: xml）
 SELECT xpath（'./ status / text（）'，col）AS status 
 FROM x 
  

/ text（） c $ c>< status> 标记。

返回 xml 的数组 - ：

  status 
 xml [] 
 ------- 
 {ERROR_MISSING_DATA } 
  

应用于您的表格

回复您的问题更新，这可以简单地是：

  SELECT id，xpath（'./ status / text response :: xml）AS status 
 FROM tbl; 
  

如果您确定每行只有一个状态标签，从数组：

  SELECT id，（xpath（'./ status / text（）'，response :: xml） [1] AS状态
 FROM tbl; 
  

如果可以有多个状态项：

  SELECT id，unnest（xpath（'./ status / text（）'，response :: xml））AS status 
 FROM tbl; 
  

每个 id

投射到 xml

您的列为 text （而不是 xml ，您需要才能投射到 xml 显式。函数 xpath（）期望类型 xml 的第二个参数，强制转换未类型化的字符串常量到 xml ，但 文本列不需要。

无需显式强制转换：

  SELECT xpath / status / text（）'
，'<？xml version =1.0？>< response>< status> SUCCESS< / status>< / response& / code> 
 
 
 
  CTE 就像我的第一个例子 needs 列中的公用表表达式。如果我没有转换为特定类型，则会使用未知类型 - 这是不是与无类型的字符串。显然，在未知和 xml 之间没有实现直接转换。您必须先转换为 text ： unknown_type_col :: text :: xml 。更好地转换为 :: xml 立即。
 
 
 
这已经收紧与PostgreSQL 9.1（我想）。 
 
 
 
无论使用哪种方法，字符串都必须是 valid xml  ）将引发异常。
 
Below is the column response from my Postgres table.  I want to extract the status from all the rows in my Postgres database. The status could be of varying sizes like SUCCESS as well so I do not want to use the substring function. Is there a way to do it?
<?xml version="1.0" ?><response><status>ERROR_MISSING_DATA</status><responseType>COUNTRY_MISSING</responseType><country_info>USA</country_info><phone_country_code>1234</phone_country_code></response>
so my table structure is like this
   Column    |            Type             |                        Modifiers                         

-------------+-----------------------------+----------------------------------------------------------

 id          | bigint                      | not null default nextval('events_id_seq'::regclass)
 hostname    | text                        | not null
 time        | timestamp without time zone | not null
 trn_type    | text                        | 
 db_ret_code | text                        | 
 request     | text                        | 
 response    | text                        | 
 wait_time   | text                        | 
And I want to extract status from each and every request.  How do i do this?

Below is a sample row.  And assume the table name abc_events
id          | 1870667
hostname    | abcd.local
time        | 2013-04-16 00:00:23.861
trn_type    | A
request     | <?xml version="1.0" ?><response><status>ERROR_MISSING_DATA</status><responseType>COUNTRY_MISSING</responseType><country_info>USA</country_info><phone_country_code>1234</phone_country_code></response>
response    | <?xml version="1.0" ?><response><status>ERROR_MISSING_DATA</status><responseType>COUNTRY_MISSING</responseType><country_info>USA</country_info><phone_country_code>1234</phone_country_code></response>

解决方案
Use the xpath() function:
WITH x(col) AS (SELECT '<?xml version="1.0" ?><response><status>ERROR_MISSING_DATA</status></response>'::xml)
SELECT xpath('./status/text()', col) AS status
FROM   x
/text() strips the surrounding <status> tag.

Returns an array of xml - with a single element in this case:
status
xml[]
-------
{ERROR_MISSING_DATA}


Applied to your table

In response to your question update, this can simply be:
SELECT id, xpath('./status/text()', response::xml) AS status
FROM   tbl;
If you are certain there is only a single status tag per row, you can simply extract the first item from the array:
SELECT id, (xpath('./status/text()', response::xml))[1] AS status
FROM   tbl;
If there can be multiple status items:
SELECT id, unnest(xpath('./status/text()', response::xml)) AS status
FROM   tbl;
Gets you 1-n rows per id.

Cast to xml

Since you defined your columns to be of type text (instead of xml, you need to cast to xml explicitly. The function xpath() expects the 2nd parameters of type xml. An untyped string constant is coerced to xml automatically, but a text column is not. You need to cast explicitly.

This works without explicit cast:
  SELECT xpath('./status/text()'
      ,'<?xml version="1.0" ?><response><status>SUCCESS</status></response>')
A CTE like in my first example needs a type for every column in the "common table expression". If I had not cast to a specific type, the type unknown would have been used - which is not the same thing as an untyped string. Obviously, there is no direct conversion implemented between unknown and xml. You'd have to cast to text first: unknown_type_col::text::xml. Better to cast to ::xml right away.

This has been tightened with PostgreSQL 9.1 (I think). Older versions were more permissive.

Either way, with any of these methods the string has to be valid xml or the cast (implicit or explicit) will raise an exception.

                        
这篇关于在PostgreSQL中提取xml标签的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！