Получить сгруппированные данные из поля XML с помощью XQUERY - SQL Server 2008

Вот как выглядит мой 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>

Мне нужно получить всю информацию о платежах для каждого сотрудника

что-то типа :

E1 0000007535 Открыть ABC 100.00

E1 0000007536 Закрыто XYZ 468,00

......

E2 0000007541 Открыть EEE 0.00
....

Но приведенный ниже код дает мне только Dispositon, Type и AmountPaid. Я не могу сопоставить его с каждым сотрудником.

;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

Спасибо ББ


sql sql-server-2008 xquery-sql
person BumbleBee    schedule 29.03.2011    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Я не очень понимаю, как этот xml относится к столбцу xmldocument в вашей таблице EMPLOYEE. Разделен ли 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)

Результат

---------- ---------- ---------- ---------- ----------
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
person Mikael Eriksson    schedule 30.03.2011