I cannot get the values col1, col2, col3. What am I doing wrong? I am using XML first timer in Postgres.

Additionally added the result XML and SQL Server Script.

Is it possible to get the same result as in SQL Server

I added more detail and fixed the errors in the question.

with cte as (SELECT 
 '<a id_file="9965_99" version="4.1.0">
  <b>
    <bbb name_code="note"/>
  </b>
  <c id="d000fe02-bd80-46ac-8d60-afdf3e194f90">
    <f ddd="test1" />
    <r rrr="1" />
  </c>
  <c id="d000fe02-bd80-46ac-8d60-afdf3e194f91">
    <f ddd="test2" />
    <r rrr="2" />
  </c>
  <c id="d0t0fe02-bd80-46ac-8d60-afdf3e194f92">
    <f ddd="test3" />
    <r rrr="3" />
  </c>
</a>' ::xml)
SELECT 
(xpath('//@id_file', xt))[1] as col1,
(xpath('//@version', xt))[1] as col2,
(xpath('//b/@bbb', xt))[1] as col3,
(xpath('//bbb/@name_code', xt))[1] as col7, 
(xpath('@id', xt))[1] as col4,
(xpath('f/@ddd', xt))[1] as col5,
(xpath('r/@rrr', xt))[1] as col6
from cte
cross join unnest(xpath('*', xml)) as xt;

Postgres result:

SQL Server result:

Below script SQL Server

DECLARE @xml XML;
SET @xml =
'
<a id_file="9965_99" version="4.1.0">
  <b>
    <bbb name_code="note"/>
  </b>
  <c id="d000fe02-bd80-46ac-8d60-afdf3e194f90">
    <f ddd="test1" />
    <r rrr="1" />
  </c>
  <c id="d000fe02-bd80-46ac-8d60-afdf3e194f91">
    <f ddd="test2" />
    <r rrr="2" />
  </c>
  <c id="d0t0fe02-bd80-46ac-8d60-afdf3e194f92">
    <f ddd="test3" />
    <r rrr="3" />
  </c>
</a>
'

SELECT 
    T2.Loc.value('(//@id_file)[1]', 'varchar(100)')             AS [id_file],
    T2.Loc.value('(//@version)[1]', 'varchar(100)')             AS [version],
    T2.Loc.value('(//bbb/@name_code)[1]', 'varchar(100)')       AS [name_code],
    T2.Loc.value('(@id)[1]', 'varchar(500)')                AS [id],
    T2.Loc.value('(f/@ddd)[1]', 'varchar(500)')         AS [ddd],
    T2.Loc.value('(r/@rrr)[1]', 'varchar(500)')         AS [rrr]
FROM @xml.nodes('//c') as T2(Loc)

CodePudding user response：

I find using xmltable() for this much easier:

with cte (doc) as (SELECT 
 '<a id_file="9965_99" version="4.1.0">
  <b>
    <bbb name_code="note"/>
  </b>
  <c id="d000fe02-bd80-46ac-8d60-afdf3e194f90">
    <f ddd="test1" />
    <r rrr="1" />
  </c>
  <c id="d000fe02-bd80-46ac-8d60-afdf3e194f91">
    <f ddd="test2" />
    <r rrr="2" />
  </c>
  <c id="d0t0fe02-bd80-46ac-8d60-afdf3e194f92">
    <f ddd="test3" />
    <r rrr="3" />
  </c>
</a>'::xml)
SELECT xt.*
from cte
  cross join xmltable('/a/c'  passing doc
                     columns 
                        col1 text path '../@id_file',
                        col2 text path '../@version',
                        col3 text path '../b/bbb/@name_code',
                        col4 text path '@id', 
                        col5 text path 'f/@ddd',
                        col6 text path 'r/@rrr') as xt
;

As you want one row per <c> tag, you need to use that as the starting path in xmltable(). So the xpath expression for each column needs to be relative to the c tag. If you don't want to hardcode the ../b/bbb path, you can alternatively use '//bbb/@name_code'