CODEWITHSUNDEEP
sqlxmloracle-database
Coding Duchess
Coding Duchess

Reputation: 6919

Extracting xml data from CLOB in Oracle and Pivoting

I have a table that has a CLOB column with the following data:

<entry xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
   <content type="application/xml">
      <m:properties>
        <d:Parameters>
            <data>
               <parameter>
                    <id>{1234}</id>
                    <value>1000</value>
               </parameter>
               <parameter>
                   <id>{3456}</id>
                   <value>John Doe</value>
               </parameter>
               <parameter>
                   <id>{6789}</id>
                    <value>NY</value>
                </parameter>
               </data>              
            </d:Parameters>
        </m:properties>
   </content>
</entry>

The ID values are stored in a separate table where {3456} would be «Name » etc.

I need to retrieve data from multiple rows and pivot it so the result is

{1234}  {3456}.    {6789}
1000.  John Doe.     NY
1001.  Jane Doe.     DE
1002.  Ivan Riveras. NJ

And potentially replace {1234} strings with actual column names from another table such as ID, Name and State

How can I do that with a query?

So far I have

SELECT xmltype(t1.column1).extract(“//data/text()”) FROM Table1 t1

But how do I break it down further and pivot?

Upvotes: 0

Views: 219

Answers (1)

Alex Poole
Alex Poole

Reputation: 191520

Extracting the values is fairly straightforward; as you want multiple node values from each XML document you can use XMLTable:

select x.code, x.value
from table1 t1
cross join xmltable(
    '/data/parameter'
    passing xmltype(t1.column1)
    columns
      code path 'id',
      value path 'value'
  ) x;

which with two sample documents gives:

CODE VALUE
{1234} 1000
{3456} John Doe
{6789} NY
{1234} 1001
{3456} Jane Doe
{6789} DE

You can then pivot those; but really you need a way to link the values together. If your table has a unique key then use that; otherwise you can use the first table's rowid as a stand-in:

select rowidtochar(t1.rowid), x.code, x.value
from table1 t1
cross join xmltable(
    '/data/parameter'
    passing xmltype(t1.column1)
    columns
      code path 'id',
      value path 'value'
  ) x;

Either way, use that value internally to pivot, but don't include it in the final select list:

select id, name, state
from (
  select rowidtochar(t1.rowid), x.code, x.value
  from table1 t1
  cross join xmltable(
      '/data/parameter'
      passing xmltype(t1.column1)
      columns
        code path 'id',
        value path 'value'
    ) x
)
pivot (
  max(value)
  for code in ('{1234}' as id, '{3456}' as name, '{6789}' as state)
);
ID NAME STATE
1000 John Doe NY
1001 Jane Doe DE

fiddle

I've hard-coded the mapping between ID values and column headings in the IN() clause; you said

And potentially replace {1234} strings with actual column names from another table such as ID, Name and State

but that would requite a dynamic pivot, as @astentx said in a comment.

As a middle-ground, if you know you will have ID/name/state but don't know what the codes are and want to look up the mapping then you could do that before pivoting, with something like:

select id, name, state
from (
  select rowidtochar(t1.rowid), t2.label, x.value
  from table1 t1
  cross join xmltable(
      '/data/parameter'
      passing xmltype(t1.column1)
      columns
        code path 'id',
        value path 'value'
    ) x
  join table2 t2 on t2.code = to_number(translate(x.code, 'x{}', 'x'))
)
pivot (
  max(value)
  for label in ('ID' as id, 'Name' as name, 'State' as state)
);

fiddle

The inner query now translates the original id from the XML to a label from your look-up table, and that is then used to pivot.

But if you don't know the label values, or how many rows you will pivot into how many columns, then you're still stuck with a dynamic pivot; or having a reporting too do that for you, perhaps.

Sorry, I did not post the full xml. Just added the rest

Your updated XML has namespaces, so you need to declare those:

select id, name, state
from (
  select rowidtochar(t1.rowid), x.code, x.value
  from table1 t1
  cross join xmltable(
      xmlnamespaces(
        default 'http://www.w3.org/2005/Atom',
        'http://schemas.microsoft.com/ado/2007/08/dataservices' as "d",
        'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata' as "m"
      ),
      '/entry/content/m:properties/d:Parameters/data/parameter'
      passing xmltype(t1.column1)
      columns
        code path 'id',
        value path 'value'
    ) x
)
pivot (
  max(value)
  for code in ('{1234}' as id, '{3456}' as name, '{6789}' as state)
);

does the first parameter of the xmltable need to be the full path or can I use //?

You can still use

      '//data/parameter'

with the namespaces declared, but I generally prefer to use the full path for clarity.

fiddle

Upvotes: 2

Related Questions