Generate columns from a table column having XML string

Question

0.00/5 (No votes)

See more:

In a table column i've data as given below

<PivotSet>
<item>
<column name = "RNO">1</column>
<column name = "MIN(INSGRP)">Record- 001</column>
</item>
<item>
<column name = "RNO">2</column>
<column name = "MIN(INSGRP)">Record- 002</column>
</item>
<item>
<column name = "RNO">3</column>
<column name = "MIN(INSGRP)">Record- 003</column>
</item>
<item>
<column name = "RNO">4</column>
<column name = "MIN(INSGRP)">Record- 004</column>
</item>
<item>
<column name = "RNO">5</column>
<column name = "MIN(INSGRP)">Record- 005</column>
</item>
<item>
<column name = "RNO">6</column>
<column name = "MIN(INSGRP)">Record- 006</column>
</item>
</PivotSet>">

so i want to generate six columns into new table from this column data given above. this is single column data of a row. Each row hold this kind of column of xml string.

Columns may be dynamic i.e. may be other 1 or 2 or .....

Result should be shown as below
RNO MIN(INSGRP)
1 Record- 001
2 Record- 002
3 Record- 003
4 Record- 004
5 Record- 005
6 Record- 006

What I have tried:

so i want to generate six columns into new table from this column data given above. this is single column data of a row. Each row hold this kind of column of xml string and may have different string.

Posted 11-Jan-17 1:39am

.NET- India

Updated 11-Jan-17 19:33pm

v6

Add a Solution

Comments

Afzaal Ahmad Zeeshan 11-Jan-17 9:34am

You need to use some sort of Xml Parser. There are many available.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2017-01-11T09:13:00

You cab use XMLTABLE to fetrch values from the XML. If the data is in varchar, change it first to XMLTYPE in the query.

Consider the following example

Create the test table

SQL

CREATE TABLE XmlTestData (
   TheData varchar2(2000)
);

Add the test row

INSERT INTO XmlTestData (TheData)
VALUES ('<PivotSet>
<item>
<column name = "RNO">1</column>
<column name = "MIN(INSGRP)">Record- 001</column>
</item>	
<item>
<column name = "RNO">2</column>
<column name = "MIN(INSGRP)">Record- 002</column>
</item>
<item>
<column name = "RNO">3</column>
<column name = "MIN(INSGRP)">Record- 003</column>
</item>
<item>
<column name = "RNO">4</column>
<column name = "MIN(INSGRP)">Record- 004</column>
</item>
<item>
<column name = "RNO">5</column>
<column name = "MIN(INSGRP)">Record- 005</column>
</item>
<item>
<column name = "RNO">6</column>
<column name = "MIN(INSGRP)">Record- 006</column>
</item>
</PivotSet>');

COMMIT;

Run the query which uses the following logic:

- select the data from the table
- extra each item as separate row
- extract the columns from a single item

SQL

SELECT TO_NUMBER(extractvalue(res.RNO, 'text()'))      AS RNO,
       TO_CHAR(extractvalue(res.MIN_INSGRP, 'text()')) AS MIN_INSGRP
FROM XmlTestData xtd,
     XMLTABLE('/PivotSet/item'
              PASSING XMLTYPE(xtd.TheData) 
              COLUMNS XmlItem XMLTYPE PATH 'column' 
              ) items,
     XMLTABLE('/'
              PASSING items.XmlItem 
              COLUMNS 
                 RNO        XMLTYPE PATH 'column[@name="RNO"]/text()',
                 MIN_INSGRP XMLTYPE PATH 'column[@name="MIN(INSGRP)"]/text()'
              ) res;

The result is

RNO   MIN_INSGRP
---   ----------
1     Record- 001
2     Record- 002
3     Record- 003
4     Record- 004
5     Record- 005
6     Record- 006