Post reply

Custom Xml and Sql Server 2008

  • April 29, 2012 at 10:51 pm

    #260630

    I have created xml from sql server 2008. My Current xml is generated using "FOR XML AUTO"

    <Customer Name="john" City="Mumbai">

    <Project Project_Name="pqr" />

    </Customer>

    <Customer Name="Rocky" City="Delhi">

    <Project Project_Name="abc" />

    <Project Project_Name="lmn" />

    </Customer>

    But I want the output like

    <Customer >

    <name>John</name>

    <city>Mumbai</city>

    <Projects>

    <project>

    <Project_Name>pqr</Project_Name>

    </project>

    </Projects>

    </Customer>

    <Customer >

    <name>Rocky</name>

    <city>Delhi</city>

    <Projects>

    <project>

    <Project_Name>abc</Project_Name>

    <Project_Name>lmn</Project_Name>

    </project>

    </Projects>

    </Customer>

    So basically i want to convert attributes of the parent element to sub-elements. And want additional customize elements. Please help me.

    Thanks in Advance.

  • April 30, 2012 at 12:00 am

    #1481028

    Please post table DDL's, sample data and your original query.

    Also try XML PATH instead of XML AUTO.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • April 30, 2012 at 12:06 am

    #1481029

    Please post the data and the query you used.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • April 30, 2012 at 1:07 am

    #1481038

    This is the SQL Query.

    SELECT Customer.Name, Customer.City, Project.Project_Name

    FROM Customer INNER JOIN

    Customer_Project ON Customer.CustomerId = Customer_Project.CustomerId INNER JOIN

    Project ON Customer_Project.ProjectId = Project.ProjectId

    FOR XML AUTO

  • April 30, 2012 at 1:15 am

    #1481041

    And now the table DDL and sample data (read the first link in my signature).

    A diagram won't help me to create the tables and to put data in it;

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • April 30, 2012 at 2:40 am

    #1481054

    I Have Sent you both. pls check it

  • April 30, 2012 at 2:44 am

    #1481057

    Almost 🙂

    The sample data in the form of INSERT statements please.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • April 30, 2012 at 3:07 am

    #1481078

    Now Check.

  • April 30, 2012 at 5:23 am

    #1481141

    Here you go:

    SELECT

    c.Name

    ,c.City

    ,(

    SELECT p.Project_Name AS "Project"

    FROM

    Customer_Project cp

    INNER JOIN

    Project p

    ON cp.ProjectId = p.ProjectId

    WHERE cp.CustomerId = c.CustomerId

    FOR XML PATH(''),TYPE

    ) AS "Projects"

    FROM Customer c

    FOR XML PATH('Customer');

    I don't know a lot about FOR XML queries (I detest them), I just read the example "Generating a value list using PATH mode" in the following MSDN article:

    Examples: Using PATH Mode

    So next time, do a little research, it won't kill you, I promise.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • April 30, 2012 at 5:43 am

    #1481154

    Actually I got the Same Answer from Other Forum.

    Yes, it is working. Thanks for helping.

  • April 30, 2012 at 5:46 am

    #1481159

    vikas.pawar (4/30/2012)

    Actually I got the Same Answer from Other Forum.

    Yes, it is working. Thanks for helping.

    But I'm sure you would have learned so much more if you wrote the query yourself.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • April 30, 2012 at 6:39 am

    #1481197

    yes..........

    Thanks

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply