How to find an xml tag information using xPath in Postgresql?

Asked

Viewed 426 times

1

I wanted to use the remote xPath, but I’m not sure how it works.

I have a table and inside it a column that stores XML's. I would like to conduct a consultation that brings the value that is within this tag as a text.

Could someone help me?

sql database postgresql query xml

  • To consult this column information, I suggest using the command like of sql: https://www.w3schools.com/sql/sql_like.asp The xPath command you wanted to use in which programming program or language?

  • I will use in JAVA, the point is that this query will be used in situations where this TAG can be changed, I know what the TAG is, but how the information within it can change... :/

  • Can you catch xPath then? And then you want to see what’s in the way? Would that be?

  • The situation is like this: - I know there is the xPath command, but I don’t know how to use it - I know that if I do SELECT colunacomxml FROM table, I can get all the XML - What I need is to bring in the query, only a certain value that is inside this XML

1 answer

0

You can use the function xpath:

xpath( xpath_exp , xml [ , nsarray ])

The function xpath evaluates the expression xpath_exp in relation to the value xml and Rretorna an array of XML values corresponding to the set of nodes produced by the Xpath expression.

The second argument must be a document XML well-trained.

The third argument, optional of the function, is a matrix of namespace mappings.

I created a table called teste two-element id that is INTand conteudo that is XML. I inserted two lines with a slightly different scheme. Both have root called <raiz> but differ in the first descendant one is <teste1> and the other is <teste2>. Then I select with the following expression Xpath '//teste1':

Schema (Postgresql V10.0)

CREATE TABLE teste (
  id INT,
  conteudo XML
);

INSERT INTO teste (id, conteudo) VALUES (1, xmlparse(document '<?xml version="1.1"?><raiz><teste1>Yeah!!Serei selecionado com XPath</teste1></raiz>'));
INSERT INTO teste (id, conteudo) VALUES (2, xmlparse(document '<?xml version="1.1"?><raiz><teste2>Hum... não irei ser selecionado com XPath</teste2></raiz>'));

Query #1

SELECT xpath('//teste1', conteudo) FROM teste;

Upshot

Query #1 Execution time: 1ms

| xpath |

| ------------------------------------------------------ |

| {"Yeah!! I’ll be selected with Xpath"} |

| {} |

Browser other questions tagged sql database postgresql query xml

You are not signed in. Login or sign up in order to post.