• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

Generating XML File from SQL database

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.

Thanks!

createTable.txt
Desired-output-sample.xml
0
SpyderG
Asked:
SpyderG
  • 4
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Is anything in the root node based on data in your table? Do you have INSERTs of sample row(s). That would be helpful.
0
 
Kevin CrossChief Technology OfficerCommented:
This should help.

References:
http://msdn.microsoft.com/en-us/library/ms190922.aspx
http://msdn.microsoft.com/en-us/library/ms177400.aspx
http://msdn.microsoft.com/en-us/library/ms187928.aspx

;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'
GROUP BY FileID
FOR XML PATH('ns0:TPDocXML')
;

Open in new window


Output:
<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" />
      </Remittance>
    </Payment>
    <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" />
      </Remittance>
    </Payment>
  </Payments>
</ns0:TPDocXML>

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!

Regards,

Kevin
0
 
TempDBACommented:
you can make use of openxml for the case.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
SpyderGAuthor Commented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
One way to do this through the bcp Utility http://daytabase.org/2011/08/20/bcp-utility/; another might be to do this through SSIS if you are familiar with that.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now