使用SQL Server将XML解析为列

Question

将XML解析为包含SQL Server的列的最简单方法是什么？例如：

<ns0:root xmlns:ns0="http://herp..."> 
    <ns1:FirstElement xmlns:ns1="http://derpyderp..."> 
     <ns1:FirstElementID>AAF11303</ns1:FirstElementID> 
     <ns1:FirstEValue>some random text</ns1:FirstEValue> 
     <ns1:SecondElement> 
      <ns1:Something>Asdsad</ns1:Something> 
      <ns1:Else> 
       <ns1:Stuff>sdf</ns1:Stuff> 
       <ns1:StuffVal>15</ns1:StuffVal> 
      </ns1:Else> 
      <ns1:Else> 
       <ns1:Stuff>jarjar</ns1:Stuff> 
       <ns1:StuffVal>16</ns1:StuffVal> 
       <ns1:StuffParam>true</ns1:StuffParam> 
      </ns1:Else> 
     </ns1:SecondElement> 
     <ns1:randValue>dosd</ns1:randValue> 
    </ns1:FirstElement> 
    <ns1:FirstElement> 
     <ns1:FirstElementID>DDF00301</ns1:FirstElementID> 
     <ns1:FirstEValue/> 
     <ns1:SecondElement> 
      <ns1:Else> 
       <ns1:Stuff>yessir</ns1:Stuff> 
       <ns1:StuffVal>0</ns1:StuffVal> 
      </ns1:Else> 
     </ns1:SecondElement>   
    </ns1:FirstElement> 
    <!-- ... times n the first element with a variating amount of children up to 15 levels deep --> 
</ns0:root> 

我想这是一个简单的列输出，即：

FIRSTELEMENTID | FIRSTEVALUE  | SOMETHING | ELSE.STUFF | ELSE.STUFFVAL | ELSE.STUFFPARAM | RANDVALUE 
'AAF11303'  |'some random text'| 'Asdasd' | 'sdf'  | 15   | NULL   | 'dosd' 
'AAF11303'  |'some random text'| 'Asdasd' | 'jarjar' | 16   | TRUE   | 'dosd' 
'DDF00301'  | NULL    | NULL  | 'yessir' | 0    | NULL   | NULL 

现在，实际的XML是很多更复杂，我见过的所有例子，到目前为止涉及手动解析XML并基本上将每个子元素作为自己的XML传递，并在脚本的下一个循环等中分别进行解析等。

是否有任何方法只是将值输出为这样？手动解析几MB的XML，其中包含元素出现的无数变体以及多少次和哪些地方会使其执行一天的操作。

我一直在想做一个CLR程序集，只是用C＃做这件事，并将结果作为表传递给SQL，但我想知道是否有另一种方式。

谢谢！

Answer 1

基本上，你可以使用nodes()和values()方法来解析你的xml（并且with xmlnamespaces来处理命名空间）。事情是这样的：

;with xmlnamespaces('http://herp...' as ns0, 'http://derpyderp...' as ns1) 
select 
    T.C.value('(../../ns1:FirstElementID/text())[1]', 'nvarchar(max)') as FirstElementID, 
    T.C.value('(../../ns1:FirstEValue/text())[1]', 'nvarchar(max)') as FirstEValue, 
    T.C.value('(../ns1:Something/text())[1]', 'nvarchar(max)') as Something, 
    T.C.value('(ns1:Stuff/text())[1]', 'nvarchar(max)') as [Else.Stuff], 
    T.C.value('(ns1:StuffVal/text())[1]', 'nvarchar(max)') as [Else.StuffVal], 
    T.C.value('(ns1:StuffParam/text())[1]', 'nvarchar(max)') as [Else.StuffParam], 
    T.C.value('(../../ns1:randValue/text())[1]', 'nvarchar(max)') as [randValue] 
from @data.nodes('ns0:root/ns1:FirstElement/ns1:SecondElement/ns1:Else') as T(C) 

每个ns0:root/ns1:FirstElement/ns1:SecondElement/ns1:Else这一个创建一个行，然后把你需要的所有值（有些是从父节点获得）。请注意，如果您的First FirstElement不包含任何ns1:SecondElement/ns1:Else节点，它将不会显示在结果集中。在这种情况下，你可能希望使用这样的查询：

;with xmlnamespaces('http://herp...' as ns0, 'http://derpyderp...' as ns1) 
select 
    F.C.value('(ns1:FirstElementID/text())[1]', 'nvarchar(max)') as FirstElementID, 
    F.C.value('(ns1:FirstEValue/text())[1]', 'nvarchar(max)') as FirstEValue, 
    S.C.value('(ns1:Something/text())[1]', 'nvarchar(max)') as Something, 
    E.C.value('(ns1:Stuff/text())[1]', 'nvarchar(max)') as [Else.Stuff], 
    E.C.value('(ns1:StuffVal/text())[1]', 'nvarchar(max)') as [Else.StuffVal], 
    E.C.value('(ns1:StuffParam/text())[1]', 'nvarchar(max)') as [Else.StuffParam], 
    F.C.value('(ns1:randValue/text())[1]', 'nvarchar(max)') as [randValue] 
from @data.nodes('ns0:root/ns1:FirstElement') as F(C) 
    outer apply F.C.nodes('ns1:SecondElement') as S(C) 
    outer apply S.C.nodes('ns1:Else') as E(C) 

sql fiddle demo