How to return multiple values of XML child Nodes SQL Server 2005

[GJParker]

I have the following example XML data

<Atd_Order_Row_Data xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance">[/URL]
  <Dealer_Export_Non_Billed>
    <Targets>
      <Target>Fish 4 Cars</Target>
      <Target>V Cars</Target>
    </Targets>
  </Dealer_Export_Non_Billed>
  <Dealer_Export>
    <Dealer_Stock_Export>
      <Partner>2nd Byte - Scott Hall Feed</Partner>
      <Source>Horizon/Cherry Picked</Source>
      <Advanced_Filter_Flag>No</Advanced_Filter_Flag>
      <Partner_ID>58</Partner_ID>
      <Allow_Feed_IDs>True</Allow_Feed_IDs>
    </Dealer_Stock_Export>
    <Dealer_Stock_Export>
      <Partner>AutoVogue</Partner>
      <Feed_ID>1234</Feed_ID>
      <Source>Dealer Web Site</Source>
      <Advanced_Filter_Flag>Yes</Advanced_Filter_Flag>
      <Partner_ID>72</Partner_ID>
      <Allow_Feed_IDs>True</Allow_Feed_IDs>
      <Advanced_Filter>
        <Advanced_Filter_ID>c5bf4fcb-7a96-4b71-9039-265394ac260a</Advanced_Filter_ID>
        <Partner>AutoVogue</Partner>
        <Make>ALFA ROMEO</Make>
        <Model />
        <Operator />
      </Advanced_Filter>
      <Advanced_Filter>
        <Advanced_Filter_ID>b71e24df-93d3-4759-a910-95fff02faba6</Advanced_Filter_ID>
        <Partner>AutoVogue</Partner>
        <Make>BENTLEY</Make>
        <Model>CONTINENTAL</Model>
        <Operator />
      </Advanced_Filter>
      <Advanced_Filter>
        <Advanced_Filter_ID>015c641f-30d6-4c9a-b09f-286524683b5d</Advanced_Filter_ID>
        <Partner>AutoVogue</Partner>
        <Make />
        <Model />
        <Operator>&gt;</Operator>
        <Price>5000</Price>
      </Advanced_Filter>
      <Advanced_Filter>
        <Advanced_Filter_ID>55d846e7-f535-4f66-b2ce-6b04f84788b3</Advanced_Filter_ID>
        <Partner>AutoVogue</Partner>
        <Make />
        <Model />
        <Operator>&lt;</Operator>
        <Price>15000</Price>
      </Advanced_Filter>
      <Advanced_Filter>
        <Advanced_Filter_ID>25239ca9-7c3f-4bd9-b2f9-adfda6cc33b5</Advanced_Filter_ID>
        <Partner>AutoVogue</Partner>
        <Make>CHRYSLER</Make>
        <Model />
        <Operator>&lt;</Operator>
        <Price>10000</Price>
      </Advanced_Filter>
      <Advanced_Filter>
        <Advanced_Filter_ID>1f108e36-ba62-4fbd-ac5c-3fa91edadb0a</Advanced_Filter_ID>
        <Partner>AutoVogue</Partner>
        <Make>DAEWOO</Make>
        <Model>KORANDO</Model>
        <Operator>&gt;</Operator>
        <Price>12000</Price>
      </Advanced_Filter>
    </Dealer_Stock_Export>
    <Dealer_Stock_Export>
      <Partner>G Forces</Partner>
      <Source>Horizon/Cherry Picked</Source>
      <Replacement_Phone_No>016130306788</Replacement_Phone_No>
      <Advanced_Filter_Flag>No</Advanced_Filter_Flag>
      <Partner_ID>126</Partner_ID>
      <Allow_Feed_IDs>False</Allow_Feed_IDs>
    </Dealer_Stock_Export>
  </Dealer_Export>
</Atd_Order_Row_Data>

I currently us ethe following XML to return the names of each dealer_stock_export/partner

SELECT Order_ID,Order_Row_ID,A.node.value('(.)[1]', 'varchar(50)') AS Target
FROM order_row_misc_data CROSS APPLY Data_xml.nodes('/Atd_Order_Row_Data/Dealer_Export/Dealer_Stock_Export/Partner') AS A(node)
WHERE Misc_Data_ID = 6000002

and this returns the values

2nd Byte - Scott Hall Feed
AutoVogue
G Forces

What I now need to do is to return the values for dealer_stock_export/Feed_ID for each of these rows so that I end up with

2nd Byte - Scott Hall Feed Null
AutoVogue 1234
G Forces Null

I have tried

SELECT Order_ID,Order_Row_ID,A.node.value('(.)[1]', 'varchar(50)') AS Target,
B.node.value('(.)[1]', 'varchar(50)') AS feed_id
FROM order_row_misc_data 
CROSS APPLY Data_xml.nodes('/Atd_Order_Row_Data/Dealer_Export/Dealer_Stock_Export/Partner') AS A(node)
OUTER APPLY Data_xml.nodes('/Atd_Order_Row_Data/Dealer_Export/Dealer_Stock_Export/Feed_ID') AS B(node)
WHERE Misc_Data_ID = 6000002

But this is not correct as it returns

2nd Byte - Scott Hall Feed 1234
AutoVogue 1234
G Forces 1234

Can anyone help

Thanks in advance








Gary Parker
MIS Data Analyst
Manchester, England