Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to send xml to file

Posted on 2011-10-23
1
Medium Priority
?
283 Views
Last Modified: 2013-12-25
I have the following script that works great.  How can I write the results (select MyXML) to a file?



DECLARE @claimXML XML
declare @myXml xml
SELECT @claimXML = (
SELECT     SourceDocumentTypeActionCode as  '@ActionCode',
            Status as '@Status',
            RecipientName as '@RecipientName',
            ClaimPayeeType '@ClaimPayeeType',
                  '' as 'Participant/@FirstName',
                  '' as  'Participant/@LastName',
                  SSN as 'Participant/@SSN',
                         ProviderName as 'Provider/@ProviderName',
                              AddressLine1 as 'Provider/MailingAddress/@AddressLine1',
                             AddressLine2 as 'Provider/MailingAddress/@AddressLine2',
                              City as 'Provider/MailingAddress/@City',
                              StateCode as 'Provider/MailingAddress/@StateCode',
                              ZipCode as 'Provider/MailingAddress/@ZipCode',
                              'US'as 'Provider/MailingAddress/@CountryCode',
                                    ExternalClaimNumber  as 'ClaimDetail/@ExternalClaimNumber',
                                    ClaimAmount  as 'ClaimDetail/@ClaimAmount',
                                    'true' as 'ClaimDetail/@ReceiptReceived',
                                    'false' as 'ClaimDetail/@ClaimOnHold',
                                    ExpenseCategory as 'ClaimDetail/@ExpenseCategory',
                                    ExpenseType as 'ClaimDetail/@ExpenseType',
                                    case when ClaimPayeeType ='PTP' then 'Provider Payment'
                                          when ClaimType =''then KeyName
                                          end as 'CustomProperties/@KeyName',
                                          case when ClaimPayeeType ='PTP' then KeyValue
                                          when ClaimType =''then KeyValue
                                          end as 'CustomProperties/@KeyValue'      

FROM        My_Table


FOR XML PATH('Claim'),TYPE)

--Start of XML File Header  
;WITH XMLNAMESPACES(
      DEFAULT 'http://www.TheUsual/2008/10/01/',
      'http://www.w3.org/2001/XMLSchema-instance' as "xsi",
      'http://www.w3.org/2001/XMLSchema' as "xsd")


SELECT @myXml = (
SELECT

      'Administrator' AS '@AdminAlias',
      COUNT(ExternalClaimNumber) AS '@TotalClaimsCount',
      getdate() as '@CreatedDateTime',
      @claimXML
FROM         MY_Table


FOR XML PATH('StandardExchange'))

select @myXml
0
Comment
Question by:kat50
1 Comment
 
LVL 25

Accepted Solution

by:
jogos earned 2000 total points
ID: 37014860
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

580 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