Get Grouped Data from XML field Using XQUERY - SQL server 2008

Here is how my xml looks like :

<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 0000007536 Closed XYZ 468.00

......

E2 0000007541 Open EEE 0.00

....

But the below code gives me only Dispositon,Type and AmountPaid I am not able to map it to each 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

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.

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