有什么方法可以将从dataTable生成的XML插入到oracle中? [英] any way to insert the XML generated from dataTable into the oracle?

问题描述

大家好，

我想知道是否有一种方法可以将从dataTable(dataTable.GetXML())生成的XML插入到oracle中吗?

在此先感谢

Hi guys,

I want to know if there is a way to insert the XML generated from dataTable(dataTable.GetXML()) into the oracle?

Thanks in advance,

推荐答案

要实现此目的，您将必须使用oracle中可用的XMLSequence函数.

假设您的XML如下:

To achieve this you will have to use XMLSequence function available in oracle.

Suppose your XML is as follows:

<theRange>
    <theRow><First>Bob</First><Last>Smith</Last><Age>30</Age></theRow>
    <theRow><First>Sue</First><Last>Jones</Last><Age>34</Age></theRow>
    <theRow><First>John</First><Last>Bates</Last><Age>40</Age></theRow>
</theRange>

然后您的查询将如下所示:

Then your query will be as follows:

WITH openedXml AS (
  SELECT extractvalue(column_value, '/theRow/First') FIRST,
         extractvalue(column_value, '/theRow/Last') LAST,
         to_number(extractvalue(column_value, '/theRow/Age')) Age
    FROM TABLE(XMLSequence(XMLTYPE('<theRange>
      <theRow><First>Bob</First><Last>Smith</Last><Age>30</Age></theRow>
      <theRow><First>Sue</First><Last>Jones</Last><Age>34</Age></theRow>
      <theRow><First>John</First><Last>Bates</Last><Age>40</Age></theRow>
  </theRange>').extract('/theRange/theRow')))
  )
  SELECT *
    FROM openedxml
   WHERE age BETWEEN 30 AND 35;

问候
Praveen

Regards
Praveen

这篇关于有什么方法可以将从dataTable生成的XML插入到oracle中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！