Mikel
[转载]SQL Server 使用OpenXML 导入xml格式的数据 – 鸿鹄_A – 博客园.
<span class="pln">declare </span><span class="lit">@xmlText</span><span class="pln"> xmldeclare </span><span class="lit">@club</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">)</span><span class="pln">DECLARE </span><span class="lit">@idoc</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"></span><span class="kwd">set</span><span class="pln"> </span><span class="lit">@xmlText</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">select</span><span class="pln"> doc </span><span class="kwd">from</span><span class="pln"> openrowset</span><span class="pun">(</span><span class="pln">bulk </span><span class="str">'D:\RF_Integration\Code\Import\RF.Integration.Import.BusinessLogic\bin\Debug\ModifiedPackageFiles\golf_b6dd63fb30f84a9b941a9cdcbbacb30f_201209041607[007-004].xml'</span><span class="pun">,</span><span class="pln">single_blob</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> t</span><span class="pun">(</span><span class="pln">doc</span><span class="pun">))</span><span class="pln">EXEC sp_xml_preparedocument </span><span class="lit">@idoc</span><span class="pln"> OUTPUT</span><span class="pun">,</span><span class="lit">@xmlText</span><span class="pln"></span><span class="kwd">set</span><span class="pln"> </span><span class="lit">@club</span><span class="pun">=</span><span class="str">'golf'</span><span class="pln">insert </span><span class="kwd">into</span><span class="pln"> </span><span class="pun">[</span><span class="pln">dbo</span><span class="pun">].</span><span class="typ">Address_Temp</span><span class="pun">([</span><span class="typ">AddressType</span><span class="pun">]</span><span class="pln"> </span><span class="pun">,[</span><span class="typ">Country</span><span class="pun">],[</span><span class="typ">PostalCity</span><span class="pun">]</span><span class="pln"> </span><span class="pun">,[</span><span class="typ">PostalNumber</span><span class="pun">],[</span><span class="typ">Street</span><span class="pun">],[</span><span class="pln">CO_Address</span><span class="pun">],[</span><span class="typ">PersonID</span><span class="pun">],[</span><span class="typ">PersonNum</span><span class="pun">],[</span><span class="typ">PersonOptType</span><span class="pun">])</span><span class="pln"></span><span class="kwd">select</span><span class="pln"> </span><span class="pun">[</span><span class="typ">AddressType</span><span class="pun">]</span><span class="pln"> </span><span class="pun">,[</span><span class="typ">Country</span><span class="pun">],[</span><span class="typ">PostalCity</span><span class="pun">]</span><span class="pln"> </span><span class="pun">,[</span><span class="typ">PostalNumber</span><span class="pun">],[</span><span class="typ">Street</span><span class="pun">],[</span><span class="pln">CO_Address</span><span class="pun">],[</span><span class="typ">PersonID</span><span class="pun">],[</span><span class="typ">PersonalNumber</span><span class="pun">]</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">[</span><span class="typ">PersonNum</span><span class="pun">],</span><span class="str">'Insert'</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">[</span><span class="typ">PersonOptType</span><span class="pun">]</span><span class="pln"></span><span class="kwd">from</span><span class="pln"> openxml</span><span class="pun">(</span><span class="lit">@Idoc</span><span class="pun">,</span><span class="str">'/persons/InsertRecord/Person/Addresses/Address'</span><span class="pun">,</span><span class="lit">2</span><span class="pun">)</span><span class="pln"></span><span class="typ">With</span><span class="pun">(</span><span class="pln"></span><span class="pun">[</span><span class="typ">AddressType</span><span class="pun">]</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"></span><span class="pun">[</span><span class="typ">Country</span><span class="pun">]</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"></span><span class="pun">[</span><span class="typ">PostalCity</span><span class="pun">]</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"></span><span class="pun">[</span><span class="typ">PostalNumber</span><span class="pun">]</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"></span><span class="pun">[</span><span class="typ">Street</span><span class="pun">]</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"></span><span class="pun">[</span><span class="pln">CO_Address</span><span class="pun">]</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"></span><span class="pun">[</span><span class="typ">PersonId</span><span class="pun">]</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">)</span><span class="pln"> </span><span class="pun">..</span><span class="com">//..//PersonId',--(此处应该为单斜杠，应为blog编辑器写单斜杠有问题，所以将其写为双斜杠)</span><span class="pln"></span><span class="pun">[</span><span class="typ">PersonalNumber</span><span class="pun">]</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">)</span><span class="pln"> </span><span class="str">'..//..//@PersonalNumber'</span><span class="pln"></span><span class="pun">)</span> |
上边的代码中， 数据库直接读取了xml文件，然后解析 xml 数据，将其保存到Address_Temp 表中，蓝色文字部门，‘..//..//PersonId‘ 表示单签节点 上两级节点 字段 PersonId 的值，如果带上‘@’ ，表示属性值。
具体的openxml 的用法，可以参看一下链接：http://www.cnblogs.com/wuming/archive/2010/01/18/1650755.html
