以下是我的Postgres表中的列响应.我想从Postgres数据库中的所有行中提取状态.状态可能有不同的大小,如SUCCESS,所以我不想使用子串功能.有办法吗？
- <?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 | 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 |
我想从每一个请求中提取状态.我该怎么做？
下面是一个示例行.并假设表名为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>
使用
xpath()功能：
- 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()剥离周围的< 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 status
- FROM tbl;
如果可以有多个状态项：
- SELECT id,unnest(xpath('./status/text()',response::xml)) AS status
- FROM tbl;
获取每个id为1-n行.
投射到xml
由于您将列定义为文本类型(而不是xml),因此您需要显式转换为xml.xpath()函数需要xml类型的第2个参数,一个无类型的字符串常量被自动强制为xml,而文本列不是,你需要明确地施放.
这没有明确的演示：
- SELECT xpath('./status/text()','<?xml version="1.0" ?><response><status>SUCCESS</status></response>')
像我第一个例子中的CTE需要一个类型为“common table expression”中的每一列.如果我没有转换为特定类型,那么将使用未知的类型 – 这与无类型的字符串不同.显然,未知和xml之间没有直接的转换.你必须先转换为文本：unknown_type_col :: text :: xml.更好地投射到:: xml马上.
这已经被Postgresql 9.1收紧了(我想).旧版本更容易.
无论哪种方式,使用任何这些方法,字符串必须是有效的xml或cast(隐式或显式)将引发异常.
