XML explicit group by parent tag is not working

Question

I required output like below. But when I execute my code, I am not getting the product properly grouped under each parent (it should be like under "US" country code we need to have three products and under "FR" we need to have two products).

<?xml version="1.0" encoding="utf-8"?>
<ComProducts>
  <Country Code="US">
    <Product>
      <manufacturername>abc</manufacturername>
      <productname>xyz road</productname>
      <upc>RJ</upc>
    </Product>
    <Product>
      <manufacturername>temp</manufacturername>
      <productname>ppp road</productname>
      <upc>RJ</upc>
    </Product>
    <Product>
      <manufacturername>ccc</manufacturername>
      <productname>oli Com</productname>
      <upc>CL</upc>
    </Product>
  </Country>
  <Country Code="FR">
    <Product>
      <manufacturername>xxx</manufacturername>
      <productname>aaa road</productname>
      <upc>NY</upc>
    </Product>
    <Product>
      <manufacturername>eee</manufacturername>
      <productname>olkiu road</productname>
      <upc>CL</upc>
    </Product>
  </Country>
</ComProducts>

CODE :

DECLARE @Products TABLE   
        (   
           code             VARCHAR(10),   
           manufacturername VARCHAR(50),   
           productname      NVARCHAR(255),   
           upc              VARCHAR(100)  

        )   

      INSERT INTO @Products   
            select  'en-us', 'abc', 'xyz road', 'RJ' union all
            select  'en-us', 'temp', 'ppp road', 'RJ' union all
            select  'fr-fr', 'xxx', 'aaa road', 'NY' union all
            select  'en-us', 'ccc', 'oli Com', 'CL' union all
            select  'fr-fr', 'eee', 'olkiu road', 'CL' 


      SELECT 1    AS Tag,   
             NULL AS Parent,   
             NULL AS 'ComProducts!1!',   
             NULL AS 'Country!2!locale',
             NULL AS 'Products!3!',   
             NULL AS 'Products!3!manufacturerName!Element',   
             NULL AS 'Products!3!productName!cdata',   
             NULL AS 'Products!3!upc!Element'
      UNION ALL   
      SELECT 2 AS Tag,   
             1 AS Parent,   
             NULL,   
             code,
             NULL,   
             manufacturername,   
             productname,   
             upc
             FROM   @Products  
      UNION ALL   
      SELECT 3 AS Tag,   
             2 AS Parent,   
             NULL,   
             NULL,
             NULL,   
             manufacturername,   
             productname,   
             upc  

      FROM   @Products 
      FOR    xml explicit

score 0 · Answer 1 · answered Jan 25 '16 at 09:19

I would recommend you to do this with for xml path instead of for xml explicit.

select P1.code as '@Code',
       (
       select P2.manufacturername,
              P2.productname,
              P2.upc
       from @Products as P2
       where P1.code = P2.code
       for xml path('Product'), type
       )
from @Products as P1
group by P1.code
for xml path('Country'), root('ComProducts');

Result:

<ComProducts>
  <Country Code="en-us">
    <Product>
      <manufacturername>abc</manufacturername>
      <productname>xyz road</productname>
      <upc>RJ</upc>
    </Product>
    <Product>
      <manufacturername>temp</manufacturername>
      <productname>ppp road</productname>
      <upc>RJ</upc>
    </Product>
    <Product>
      <manufacturername>ccc</manufacturername>
      <productname>oli Com</productname>
      <upc>CL</upc>
    </Product>
  </Country>
  <Country Code="fr-fr">
    <Product>
      <manufacturername>xxx</manufacturername>
      <productname>aaa road</productname>
      <upc>NY</upc>
    </Product>
    <Product>
      <manufacturername>eee</manufacturername>
      <productname>olkiu road</productname>
      <upc>CL</upc>
    </Product>
  </Country>
</ComProducts>

XML explicit group by parent tag is not working

1 Answers1