XML: building it and reading it

JSON won the web, but XML still runs banks, governments, healthcare and every enterprise integration built before 2010. Oracle's XML functions mirror the JSON ones:

Test 1: build a scorecard as XML

Match 32 as XML: a match element with id and winner attributes, and an innings element per innings with no and team attributes and runs and wickets child elements.

Way 1: XMLELEMENT and XMLAGG

with inn as (
  select b.match_id, b.innings_no, t.team_code, sum(b.total_runs) as runs, sum(b.is_wicket) as wickets
  from   cr_balls_v b
  join   cr_teams t on t.team_id = b.batting_team_id
  where  b.match_id = 32
  group  by b.match_id, b.innings_no, t.team_code
)
select xmlserialize(content
         xmlelement("match",
           xmlattributes(m.match_id as "id", w.team_code as "winner"),
           (select xmlagg(xmlelement("innings",
                            xmlattributes(i.innings_no as "no", i.team_code as "team"),
                            xmlforest(i.runs as "runs", i.wickets as "wickets"))
                          order by i.innings_no)
            from inn i))
         as varchar2(4000)) as doc
from   cr_matches m
join   cr_teams w on w.team_id = m.winner_id
where  m.match_id = 32;

• XMLATTRIBUTES makes attributes (id="32"); XMLFOREST makes one child element per value.
• Element names in double quotes keep their case: "match", not MATCH.
• XMLSERIALIZE(CONTENT ... AS VARCHAR2(4000)) turns the XML value into text.

Way 2: string concatenation

with inn as (
  select b.innings_no, t.team_code, sum(b.total_runs) as runs, sum(b.is_wicket) as wickets
  from   cr_balls_v b
  join   cr_teams t on t.team_id = b.batting_team_id
  where  b.match_id = 32
  group  by b.innings_no, t.team_code
)
select '<match id="32" winner="LDS">'
       || listagg('<innings no="' || innings_no || '" team="' || team_code || '"><runs>' || runs
                  || '</runs><wickets>' || wickets || '</wickets></innings>')
          within group (order by innings_no)
       || '</match>' as doc
from   inn;

Same text, same warning as for JSON: the first & or < in a value breaks the document. The XML functions escape them (&, <) for you.

Test 2: read XML into rows

From that XML, one row per innings: number, team, runs and wickets.

Way 1: XMLTABLE

select x.innings_no, x.team, x.runs, x.wickets
from   xmltable('/match/innings'
         passing xmltype('<match id="32" winner="LDS">'
                      || '<innings no="1" team="CHN"><runs>160</runs><wickets>10</wickets></innings>'
                      || '<innings no="2" team="LDS"><runs>163</runs><wickets>4</wickets></innings></match>')
         columns innings_no number      path '@no',
                 team       varchar2(3) path '@team',
                 runs       number      path 'runs',
                 wickets    number      path 'wickets') x
order  by x.innings_no;

The row path /match/innings makes a row per innings element; each column's path is relative to it. @no is an attribute, runs a child element.

Way 2: regular expressions (please don't)

with doc as (
  select '<match id="32" winner="LDS">'
      || '<innings no="1" team="CHN"><runs>160</runs><wickets>10</wickets></innings>'
      || '<innings no="2" team="LDS"><runs>163</runs><wickets>4</wickets></innings></match>' as x
  from dual
)
select to_number(regexp_substr(x, '<innings no="(\d+)"', 1, level, null, 1))            as innings_no,
       regexp_substr(x, 'team="(\w+)"', 1, level, null, 1)                               as team,
       to_number(regexp_substr(x, '<runs>(\d+)</runs>', 1, level, null, 1))              as runs,
       to_number(regexp_substr(x, '<wickets>(\d+)</wickets>', 1, level, null, 1))        as wickets
from   doc
connect by level <= regexp_count(x, '<innings ')
order  by innings_no;

It gives the right answer here, and breaks the day someone reorders the attributes, adds a namespace, or puts a comment in. XML has a parser for a reason.

19c and 26ai

The same on both.

Gotchas

• Namespaces. Real-world XML usually has them (xmlns="..."), and then /match/innings finds nothing until you add XMLNAMESPACES(DEFAULT '...') to the XMLTABLE.
• EXTRACTVALUE and EXTRACT are deprecated. Use XMLTABLE, or XMLCAST(XMLQUERY(...)) for one value.
• XMLTYPE text is case-sensitive, element names included: /Match doesn't find <match>.