Extraction de la valeur de la balise xml dans PostgreSQL

Ci-dessous est la colonne réponse de mon Postgres table. Je veux extraire le statut de toutes les lignes dans ma base de données Postgres. L'état peut être de différentes tailles comme SUCCESS ainsi donc, je ne veux pas utiliser la fonction de sous-chaîne. Est-il un moyen de le faire?

<?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>

donc ma structure de la table est comme ça

   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                        |

Et je veux extraire le statut de chaque demande. Comment puis-je faire cela?

Ci-dessous est un exemple de ligne. Et d'assumer le nom de la table 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>

source d'informationauteur ronak

  1. 13

    Utiliser le xpath() fonction:

    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() bandes les environs <status> tag.

    Retourne un tableau de xml - avec un élément unique dans ce cas:

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

    Appliqué à votre table

    En réponse à votre question de mise à jour, cela peut tout simplement être:

    SELECT id, xpath('./status/text()', response::xml) AS status
FROM   tbl;

    Si vous êtes certain qu'il y est un seul état de la balise par ligne, vous pouvez simplement extraire le premier élément du tableau:

    SELECT id, (xpath('./status/text()', response::xml))[1] AS status
FROM   tbl;

    Si il peut y avoir plusieurs éléments d'état:

    SELECT id, unnest(xpath('./status/text()', response::xml)) AS status
FROM   tbl;

    Vous reçoit de 1 à n lignes par id.

    Fonte à xml

    Depuis que vous avez défini vos colonnes pour être de type text (au lieu de xmlvous besoin cast vers xml explicitement. La fonction xpath() attend le 2ème paramètres de type xml. Un non constante de chaîne est contraint à xml automatiquement, mais un text colonne ne l'est pas. Vous devez effectuer un cast explicite.

    Cela fonctionne sans cast explicite:

      SELECT xpath('./status/text()'
      ,'<?xml version="1.0" ?><response><status>SUCCESS</status></response>')

    Un CTE comme dans mon premier exemple besoins un type pour chaque colonne de la "expression de table commune". Si je n'avais pas jeté pour un type spécifique, le type unknown aurait été utilisée - ce qui est pas la même chose comme un de type chaîne de. Évidemment, il n'y a pas de conversion directe mis en place entre unknown et xml. Vous avez le cast de text première: unknown_type_col::text::xml. Mieux de jeter à ::xml tout de suite.

    Cela a été renforcée avec PostgreSQL 9.1 (je pense). Les anciennes versions étaient plus permissive.

    De toute façon, avec une de ces méthodes, la chaîne doit être xml valide ou de la fonte (implicite ou explicite) génère une exception.