Get XML value from XML data

Question

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', xm))
from cte;

Output:

unnest
-----------------
<name>Sam</name>
<name>Mak</name>

Expected output:

unnest
-----------------
Sam
Mak

Answer 1

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  |