英文:
Get XML value from XML data
问题
I want to get only some part of XML value.
Given sample data:
<Root>
<row id="1"><name>Sam</name><address>dummy address</address></row>
<row id="2"><name>Mak</name></row>
<row id="3" />
</Root>
I want to return only name values Sam,Mak.
My try:
with cte as
(
select '<Root>
<row id="1"><name>Sam</name><address>dummy address</address></row>
<row id="2"><name>Mak</name></row>
<row id="3" />
</Root>'::xml as xm
)
select unnest(xpath('//Root/row/name/text()', xm))
from cte;
Expected output:
unnest
-----------------
Sam
Mak
英文:
I want to get only some part of XML value.
Given sample data:
<!-- language: XML -->
<Root>
<row id="1"><name>Sam</name><address>dummy address</address></row>
<row id="2"><name>Mak</name></row>
<row id="3" />
</Root>
I want to return only name values Sam,Mak.
My try:
with cte as
(
select '<Root>
<row id="1"><name>Sam</name><address>dummy address</address></row>
<row id="2"><name>Mak</name></row>
<row id="3" />
</Root>'::xml as xm
)
select unnest(xpath('//Root/row/name', xm))
from cte;
Output:
unnest
-----------------
<name>Sam</name>
<name>Mak</name>
Expected output:
unnest
-----------------
Sam
Mak
答案1
得分: 1
这通常更容易使用 xmltable() 函数来实现：
with cte as
(
select ''<Root>
<row id="1"><name>Sam</name><address>dummy address</address></row>
<row id="2"><name>Mak</name></row>
<row id="3" />
</Root>'::xml as xm
)
select p.*
from cte
cross join xmltable('/Root/row' passing xm
columns id text path '@id',
name text path 'name') as p;
结果：
id | name
---+-----
1 | Sam
2 | Mak
3 |
英文:
This is typically easier with xmltable()
with cte as
(
select '<Root>
<row id="1"><name>Sam</name><address>dummy address</address></row>
<row id="2"><name>Mak</name></row>
<row id="3" />
</Root>'::xml as xm
)
select p.*
from cte
cross join xmltable('/Root/row' passing xm
columns id text path '@id',
name text path 'name') as p;
Returns:
id | name
---+-----
1 | Sam
2 | Mak
3 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论