从具有XML字符串的表列生成列

问题描述

在表格栏中，我的数据如下：



< 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）>记录 - 002< / column>

< / item>

< item>

<列名=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>>



所以我想生成六个c从上面给出的这一列数据中的olumns到新表。这是一行的单列数据。每一行都包含这种xml字符串列。



列可能是动态的，也可能是其他1或2或.....



结果应如下所示

RNO MIN（INSGRP）

1记录 - 001

2记录 - 002

3记录 - 003

4记录 - 004

5记录 - 005

6记录 - 006



我尝试了什么：



所以我想从上面给出的这个列数据中生成六列到新表中。这是一行的单列数据。每行都包含这种xml字符串列，并且可能有不同的字符串。

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.

推荐答案

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

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

这篇关于从具有XML字符串的表列生成列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！