SQL使用OpenXML检索多个元素

问题描述：

我在查询使用OpenXML检索XML中的主题元素之间的帽元素我不想在支持元素之间的上限。该查询很适合检索一个值，但在有多个元素节点时会失败。SQL使用OpenXML检索多个元素

 <First> 
     <Test id="83847"> 
      <subject> 
       <cap>15</cap> 
       <cap>25</cap> 
       <cap>100</cap> 
      </subject> 
      <support> 
       <cap>9</cap> 
      </support> 
      </Test> 
     <Test id="83848"> 
      <subject> 
       <cap>150</cap> 
       <cap>2</cap> 
       <cap>10</cap> 
      </subject> 
      <support> 
       <cap>9</cap> 
      </support> 
      </Test> 
     </First> 

CREATE Table #XmlTemp(XmlField Xml); 
Set Nocount On; 
Insert Into #XmlTemp(XmlField) 
Select '<First> 
     <Test id="83847"> 
      <subject> 
       <cap>15</cap> 
       <cap>25</cap> 
       <cap>100</cap> 
      </subject> 
      <support> 
       <cap>9</cap> 
      </support> 
      </Test> 
     <Test id="83848"> 
      <subject> 
       <cap>150</cap> 
       <cap>2</cap> 
       <cap>10</cap> 
      </subject> 
      <support> 
       <cap>9</cap> 
      </support> 
      </Test> 
     </First>'As XmlField; 

Declare @xmlData Xml; 
Select @xmlData = XmlField From #XmlTemp; 

Declare @document int; 
Exec sp_xml_preparedocument @document Output, @xmlData, NULL; 


SELECT ID,Cap FROM(
SELECT ID,Cap FROM OpenXml(@document,'./First/Test', 0) With (ID varchar(max)'./@id', Cap Varchar(max) './subject/cap')) alias 

drop table #xmltemp

这将会是相当费时更改查询使用.nodes方法，更是因为参与这样的测试，我想如果可能的话，留下来为OpenXML的时间。我只想检索出ID，然后检索多个cap元素值。

谢谢你的时间。

'从OPENXML'与相应的SP准备和删除文档已过时，不应再使用（极少有例外）。而是使用适当的[XML数据类型提供的方法]（https://msdn.microsoft.com/en-us/library/ms190798.aspx）。 – Shnugo

答

我看不到为什么使用.nodes的查询很复杂。只是

SELECT t.n.value('(/First/Test/@id)[1]', 'int') id 
    , t.n.value('(.)[1]', 'int') cap 
from @xmlData.nodes('./First/Test/subject/cap') t(n);

而且OpenXML的版本

SELECT ID,Cap FROM(
    SELECT ID,Cap 
    FROM OpenXml(@document,'./First/Test/subject/cap', 0) 
     With (ID varchar(max) '/First/Test/@id' 
      , Cap Varchar(max) '.')) alias

版本的编辑问题

SELECT ID,Cap FROM(
    SELECT ID,Cap 
    FROM OpenXml(@document,'/First/Test/subject/cap', 0) 
     With (ID varchar(max) '../../@id' 
      , Cap Varchar(max) '.')) alias

它只返回subject/cap和@id适当父：

ID Cap 
1 83847 15 
2 83847 25 
3 83847 100 
4 83848 150 
5 83848 2 
6 83848 10

不复杂。在测试希望避免的更改时，只需要额外的开销。 – Jt2ouan

我明白了。看编辑版本。 – Serg

我想这使得它有点更具挑战性，但是XML更长，并且还有其他cap元素，但是我只想要主题元素之间不支持的元素。您的代码只抓取第一个id属性，而可能有多个。看到编辑的问题。谢谢！ – Jt2ouan

答

您的XML是双重嵌套的。您在<First>之内有个<Test>个元素，并且在<subject>之间又有1:n个<cap>个元素。

查询正确的方法是潜入XML严格向前：

CREATE Table #XmlTemp(XmlField Xml); 
Set Nocount On; 
Insert Into #XmlTemp(XmlField) 
Select '<First> 
     <Test id="83847"> 
      <subject> 
       <cap>15</cap> 
       <cap>25</cap> 
       <cap>100</cap> 
      </subject> 
      <support> 
       <cap>9</cap> 
      </support> 
      </Test> 
     <Test id="83848"> 
      <subject> 
       <cap>150</cap> 
       <cap>2</cap> 
       <cap>10</cap> 
      </subject> 
      <support> 
       <cap>9</cap> 
      </support> 
      </Test> 
     </First>'As XmlField;

--The查询将使用.nodes()让所有<Test>元素，并再次.nodes()得到相关<cap>元素：

 SELECT t.value('@id', 'int') id 
      ,c.value('text()[1]', 'int') cap 
    from #XmlTemp AS tbl 
    CROSS APPLY tbl.XmlField.nodes('/First/Test') AS A(t) 
    CROSS APPLY A.t.nodes('subject/cap') AS B(c); 
GO 
DROP TABLE #XmlTemp;

SQL使用OpenXML检索多个元素

相关推荐