从具有XML字符串的表列生成列 [英] Generate columns from a table column having XML string
问题描述
在表格栏中，我的数据如下：
< 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.
推荐答案
您可以使用XMLTABLE从XML中获取值。如果数据在varchar中，请先在查询中将其更改为XMLTYPE。
请考虑以下示例
创建测试表
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
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字符串的表列生成列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
