Generating XML File from SQL database

Posted on 2011-10-18
Last Modified: 2012-05-12
have a situation where i need to convert data stored in a SQL database into a XML file with a set structure. Attached are the script used to create the table and a sample of the desired output. Any help would be greatly appreciated.


Question by:SpyderG
    LVL 59

    Expert Comment

    by:Kevin Cross
    Is anything in the root node based on data in your table? Do you have INSERTs of sample row(s). That would be helpful.
    LVL 59

    Expert Comment

    by:Kevin Cross
    This should help.


    ;WITH XMLNAMESPACES ( 'http://EAI' AS ns0 )
    SELECT FileID AS "@FileID"
         , 1.0 AS "@Version"
         , (
           SELECT t2.PaymentID AS "@PaymentID"
                , t2.PFPayerName AS "PayFrom/@PFPayerName"
                , t2.PTPayeeID AS "PayTo/@PTPayeeID"
                , t2.PTPayeeName AS "PayTo/@PTPayeeName"
                , CONVERT(DECIMAL(12,2), t2.PTAmount) AS "PayTo/@PTAmount"
                , t2.Type AS "Remittance/@Type"
                , CONVERT(CHAR(10), t2.PayerDocumentDate, 101) AS "Remittance/RemittanceRecord/@PayerDocumentDate"
                , t2.PayerDocumentType AS "Remittance/RemittanceRecord/@PayerDocumentType"
                , t2.PayerDocumentNumber AS "Remittance/RemittanceRecord/@PayerDocumentNumber"
                , CONVERT(CHAR(10), t2.PayeeDocumentDate, 101) AS "Remittance/RemittanceRecord/@PayeeDocumentDate"
                , t2.PayeeDocumentType AS "Remittance/RemittanceRecord/@PayeeDocumentType"
                , t2.PayeeDocumentNumber AS "Remittance/RemittanceRecord/@PayeeDocumentNumber"
                , CONVERT(DECIMAL(12,2), t2.AmountPaid) AS "Remittance/RemittanceRecord/@AmountPaid"
           FROM dbo.TravelexTemp t2
           WHERE t2.FileID = t1.FileID
           FOR XML PATH('Payment'), TYPE, ROOT('Payments')
    FROM dbo.TravelexTemp t1
    WHERE FileID = 'AcmeInc00608155488'
    FOR XML PATH('ns0:TPDocXML')

    Open in new window

    <ns0:TPDocXML xmlns:ns0="http://EAI" FileID="AcmeInc00608155488" Version="1.0">
      <Payments xmlns:ns0="http://EAI">
        <Payment PaymentID="000022">
          <PayFrom PFPayerName="Acme Inc" />
          <PayTo PTPayeeID="1126050" PTPayeeName="ABC Corp" PTAmount="150.29" />
          <Remittance Type="XML">
            <RemittanceRecord PayerDocumentDate="05/06/2010" PayerDocumentType="CK" PayerDocumentNumber="234946" PayeeDocumentDate="05/06/2010" PayeeDocumentType="IV" PayeeDocumentNumber="81799" AmountPaid="150.29" />
        <Payment PaymentID="000023">
          <PayFrom PFPayerName="Acme Inc" />
          <PayTo PTPayeeID="1126050" PTPayeeName="ABC Corp" PTAmount="51418.29" />
          <Remittance Type="XML">
            <RemittanceRecord PayerDocumentDate="05/06/2010" PayerDocumentType="CK" PayerDocumentNumber="234946" PayeeDocumentDate="05/06/2010" PayeeDocumentType="IV" PayeeDocumentNumber="81799" AmountPaid="51418.29" />

    Open in new window

    Note: namespace gets associated to the Payments node also. Additionally, notice the namespace declaration is not at the end if that matters. Hopefully everything else gives you an idea of how to get to final solution via ordering, etc.

    You can research XML Schema Documents in SQL or use of FOR XML EXPLICIT if you need more control.

    Hopefully that helps!


    LVL 25

    Expert Comment

    you can make use of openxml for the case.
    LVL 59

    Expert Comment

    by:Kevin Cross
    How exactly does that work with OPENXML, TempDBA? My understanding is it is only for parsing/reading XML to row sets and not the other way around.

    Author Comment

    Thank you very much for your help Kevin, this is a great help. Only peice i am still unsure of is how to get this output to save to an XML file. Are you able to assist?

    Thanks so much.
    LVL 59

    Accepted Solution

    One way to do this through the bcp Utility; another might be to do this through SSIS if you are familiar with that.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    combine data in sql 1 23
    count vs exists 9 33
    Physical server SQL Server swap file size and memory allocation ? 4 45
    SQL Query 2 45
    When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now