从XML字段获取分组数据使用XQUERY - SQL server 2008

Here is how my xml looks like :

这是我的xml的样子:

<Company xmlns ="http://abc.com/rules">
<Employee id="E1" number="0000007535">
   <Payment disposition="Open" type="ABC" amount_paid="100.00" />
   <Payment disposition="Closed" type="XYZ" amount_paid="468.00" />
   <Payment disposition="Open" type="AOD" amount_paid="156.00" />
   <Payment disposition="Closed" type="ONB" amount_paid="2834.00" />
</ Employee >
<Employee id="E1" number="0000007536">
   <Payment disposition="Open" type="DFG" amount_paid="200.00" />
   <Payment disposition="Closed" type="HFK" amount_paid="568.00" />
</ Employee >
<Employee id="E1" number="0000007537">
   <Payment disposition="Open" type="TTT" amount_paid="600.00" />
   <Payment disposition="Closed" type="BBB" amount_paid="368.00" />
</ Employee >
<Employee id="E2" number="0000007541">
   <Payment disposition="Open" type="EEE" amount_paid="0.00" />
   <Payment disposition="Closed" type="WWW" amount_paid="568.00" />
   <Payment disposition="Closed" type="GHW" amount_paid="968.00" />
</ Employee >
<Employee id="E2" number="0000007542">
   <Payment disposition="Open" type="QQQ" amount_paid="140.00" />
   <Payment disposition="Closed" type="CCC" amount_paid="68.00" />
</ Employee >
<Employee id="E3" number="0000007551">
   <Payment disposition="Open" type="AAA" amount_paid="300.00" />
   <Payment disposition="Closed" type="TTT" amount_paid="668.00" />
</ Employee >
</ Company>

I need to get all the Payment info for each Employee

我需要获得每位员工的所有付款信息

some thing like :

就像是 :

E1 0000007535 Open ABC 100.00

E1 0000007535打开ABC 100.00

E1 0000007536 Closed XYZ 468.00

E1 0000007536已关闭XYZ 468.00

......

E2 0000007541 Open EEE 0.00
....

E2 0000007541开启EEE 0.00 ....

But the below code gives me only Dispositon,Type and AmountPaid I am not able to map it to each Employee.

但是下面的代码只给我Dispositon,Type和AmountPaid我无法将它映射到每个Employee。

;WITH XMLNAMESPACES (DEFAULT 'http://abc.com/rules') 
 select   Disposition,Type,AmountPaid from 
        EMPLOYEE
     OUTER APPLY
     (    
     SELECT 
     tbl.col.value('(@disposition)[1]','varchar(20)') AS Disposition, 
  tbl.col.value('(@type)[1]','varchar(20)') AS Type,
  tbl.col.value('(@amount_paid)[1]','varchar(20)') AS AmountPaid 

   FROM xmldocument.nodes('//Employee/Payment') AS tbl(col) 

 )Z  
WHERE xmlid = 500

Thanks BB

1 个解决方案

#1

I do not really understand how this xml relates to the xmldocument column in your EMPLOYEE table. Is the xml split between rows in employee or do one employee have more than one employee in the xml? Anyway, here is a way to query the XML you have provided. Perhaps you can use this and adapt it to your situation.

我真的不明白这个xml如何与你的EMPLOYEE表中的xmldocument列相关。 xml是在employee中的行之间拆分还是一个员工在xml中有多个员工?无论如何,这是一种查询您提供的XML的方法。也许您可以使用它并根据您的情况进行调整。

declare @xmldocument xml = '
<Company xmlns="http://abc.com/rules">
    <Employee id="E1" number="0000007535">
        <Payment disposition="Open" type="ABC" amount_paid="100.00"/>
        <Payment disposition="Closed" type="XYZ" amount_paid="468.00"/>
        <Payment disposition="Open" type="AOD" amount_paid="156.00"/>
        <Payment disposition="Closed" type="ONB" amount_paid="2834.00"/>
    </Employee>
    <Employee id="E1" number="0000007536">
        <Payment disposition="Open" type="DFG" amount_paid="200.00"/>
        <Payment disposition="Closed" type="HFK" amount_paid="568.00"/>
    </Employee>
    <Employee id="E1" number="0000007537">
        <Payment disposition="Open" type="TTT" amount_paid="600.00"/>
        <Payment disposition="Closed" type="BBB" amount_paid="368.00"/>
    </Employee>
    <Employee id="E2" number="0000007541">
        <Payment disposition="Open" type="EEE" amount_paid="0.00"/>
        <Payment disposition="Closed" type="WWW" amount_paid="568.00"/>
        <Payment disposition="Closed" type="GHW" amount_paid="968.00"/>
    </Employee>
    <Employee id="E2" number="0000007542">
        <Payment disposition="Open" type="QQQ" amount_paid="140.00"/>
        <Payment disposition="Closed" type="CCC" amount_paid="68.00"/>
    </Employee>
    <Employee id="E3" number="0000007551">
        <Payment disposition="Open" type="AAA" amount_paid="300.00"/>
        <Payment disposition="Closed" type="TTT" amount_paid="668.00"/>
    </Employee>
</Company>'

;with xmlnamespaces(default 'http://abc.com/rules')
select 
  p.value('../@id', 'varchar(10)'),
  p.value('../@number', 'varchar(10)'),
  p.value('@disposition', 'varchar(10)'),
  p.value('@type', 'varchar(10)'),
  p.value('@amount_paid', 'varchar(10)')
from @xmldocument.nodes('Company/Employee/Payment') n(p)

Result

---------- ---------- ---------- ---------- ----------
E1         0000007535 Open       ABC        100.00
E1         0000007535 Closed     XYZ        468.00
E1         0000007535 Open       AOD        156.00
E1         0000007535 Closed     ONB        2834.00
E1         0000007536 Open       DFG        200.00
E1         0000007536 Closed     HFK        568.00
E1         0000007537 Open       TTT        600.00
E1         0000007537 Closed     BBB        368.00
E2         0000007541 Open       EEE        0.00
E2         0000007541 Closed     WWW        568.00
E2         0000007541 Closed     GHW        968.00
E2         0000007542 Open       QQQ        140.00
E2         0000007542 Closed     CCC        68.00
E3         0000007551 Open       AAA        300.00
E3         0000007551 Closed     TTT        668.00