XML explicit output: group by parent tag is not working

Question

1.00/5 (1 vote)

See more:

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). I have placed code also here and Pls help me ASAP.

XML

<?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 :

SQL

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

Posted 2-Mar-15 1:21am

md_azy

Add a Solution

Comments

Maciej Los 2-Mar-15 9:17am

ANd the issue is...

md_azy 3-Mar-15 0:38am

I am not getting xml output like what I mentioned above. It should be like under "US" country code we need to have three products and under "FR" we need to have two products).

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

John C Rayan · Answer 1 · 2015-03-02T09:28:00

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 p1.code as '@Code',
    (
     SELECT 1 as Tag,
            0 as Parent, 
            p2.manufacturername as [Product!1!manufacturername!ELEMENT],
            p2.productname as [Product!1!productname!CDATA], 
            p2.upc as [Product!1!upc!ELEMENT] 
     FROM @Products p2
     WHERE p1.code = p2.code
     FOR XML EXPLICIT
    )
FROM @Products p1 
GROUP BY p1.code
FOR XML PATH ('Country'), ROOT ('ComProducts')