解析XML文档SQL服务器内存储的过程

问题描述：

<root> 
    <EMPLOYEE ID= 100> 
    <PERIOD>AUG-2010</PERIOD> 
     <earnings> 
       <title>BASIC</title> 
     <amount>2000</amount> 
     <title>HRA</title> 
     <amount>1000</amount> 
     <title>CONVEYANCE</title> 
     <amount>500</amount> 
     </earnings> 
    </EMPLOYEE> 
    <EMPLOYEE ID= 101> 
    <PERIOD>AUG-2010</PERIOD> 
      <earnings> 
       <title>BASIC</title> 
     <amount>2000</amount> 
     <title>HRA</title> 
     <amount>400</amount> 
     <title>CONVEYANCE</title> 
     <amount>500</amount> 
     </earnings> 
    </EMPLOYEE> 

<EMPLOYEE ID= 102> 
    <PERIOD>AUG-2010</PERIOD> 
      <earnings> 
       <title>BASIC</title> 
     <amount>2000</amount> 
     <title>HRA</title> 
     <amount>800</amount> 
     <title>CONVEYANCE</title> 
     <amount>5000</amount> 
     </earnings> 
</EMPLOYEE> 
</root>

传递到存储过程的xml文档我需要存储上述信息到2表，即：工资单和payheaddetails。我想我必须循环访问xml文档。外环给我的员工ID和周期，然后我插入payslipdetails表的字段，然后进入内循环，我想插入payheaddetailswith同雇员和他的所有收入deatls像

empid title amount 
100  basic 2000 
100  hra  1000 
100  conveyance 500

然后我去外部循环，并获得下一个员工ID并重复相同的事情

我怎么能去像内部的孩子XML任何方式像openxml等.. ??

答

首先，这是不是有效的XML：

<EMPLOYEE ID= 102> 
    <PERIOD>AUG-2010</PERIOD> 
      <earnings> 
       <title>BASIC</title> 
     <amount>2000</amount> 
     <title>HRA</title> 
     <amount>800</amount> 
     <title>CONVEYANCE</title> 
     <amount>5000</amount> 
     </earnings> 
</EMPLOYEE>

的ID=属性必须由数据紧跟 - 最好在双引号 - 这是有效的：

<EMPLOYEE ID="102">

下一页：在<earnings>标记内部没有容器的情况下，您有多个<title>..</title><amount>...</amount>标记对，这使得解析几乎不可能（或者真的很麻烦）.....

<earnings> 
    <title>BASIC</title> 
    <amount>2000</amount> 
    <title>HRA</title> 
    <amount>800</amount> 
    <title>CONVEYANCE</title> 
    <amount>5000</amount> 
</earnings>

如果有可能，尽量将其更改为这样的事情：

<earnings> 
    <earning> 
    <title>BASIC</title> 
    <amount>2000</amount> 
    </earning> 
    <earning> 
    <title>HRA</title> 
    <amount>800</amount> 
    </earning> 
    <earning> 
    <title>CONVEYANCE</title> 
    <amount>5000</amount> 
    </earning> 
</earnings>

这将是更容易处理！

如果有额外<earning>容器包围<title>/<amount>对，那么你可以很容易地写这个XQuery语句和处理您的所有需求不乱，慢游标产品总数：

SELECT 
    RootData.Employee.value('(@ID)[1]', 'int') AS 'EmployeeID', 
    E.E2.value('(title)[1]', 'varchar(50)') AS 'Title', 
    E.E2.value('(amount)[1]', 'decimal(18,4)') AS 'Amount' 
from 
    (your XML column).nodes('/root/EMPLOYEE') AS RootData(Employee) 
CROSS APPLY 
    RootData.Employee.nodes('earnings/earning') AS E(E2)

，你会得到这样的输出：

EmployeeID Title   Amount 
    100  BASIC   2000.0000 
    100  HRA   1000.0000 
    100  CONVEYANCE  500.0000 
    101  BASIC   2000.0000 
    101  HRA    400.0000 
    101  CONVEYANCE  500.0000 
    102  BASIC   2000.0000 
    102  HRA    800.0000 
    102  CONVEYANCE  5000.0000

解析XML文档SQL服务器内存储的过程

相关推荐