Query to XML File 1

[ptheriault]

I will preface my questions here by saying this is the first time I have had to work with XML files.
I have the following query which I have been asked to have spit out an XML report nightly. What is that best way to accomplish this? I ran the query out put to a file to see what would happen but I get and error when I try to open the file.

Invalid at the top level of the document. Error processing resource 'file:///C:/atemp/file.xml'. Line 1, Position 1

XML_F52E2B61-18A1-11d1-B105-00805F49916B

Any thoughts?

[COLOR=blue]SET[/color] [COLOR=#FF00FF]ROWCOUNT[/color] 200
[COLOR=blue]SELECT[/color] [COLOR=red]'1'[/color] [COLOR=blue]AS[/color] account_level
       ,[COLOR=red]'FirstComp'[/color] [COLOR=blue]AS[/color] account_number
       ,[COLOR=red]''[/color] [COLOR=blue]AS[/color] parent_account_number
       ,[COLOR=red]'FirstComp Insurance'[/color] [COLOR=blue]AS[/color] account_name
       ,[COLOR=red]''[/color] [COLOR=blue]AS[/color] policy_number
       ,[COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]Varchar[/color](1),[COLOR=red]'W'[/color]) [COLOR=blue]AS[/color] coverage_type
       ,[COLOR=red]''[/color] [COLOR=blue]AS[/color] effective_date
       ,[COLOR=red]''[/color] [COLOR=blue]AS[/color] termination_date
       ,[COLOR=red]''[/color] [COLOR=blue]AS[/color] address_1
       ,[COLOR=red]''[/color] [COLOR=blue]as[/color] city
       ,[COLOR=red]''[/color] [COLOR=blue]as[/color] state
       ,[COLOR=red]''[/color] [COLOR=blue]as[/color] zip
[COLOR=blue]FROM[/color] policy_InsuredClaims_IC [COLOR=#FF00FF]Record[/color]
UNION
[COLOR=blue]SELECT[/color] [COLOR=red]'2'[/color] [COLOR=blue]AS[/color] account_level
       ,[COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]Varchar[/color](25),[COLOR=#FF00FF]Record[/color].PP_PaperID) [COLOR=blue]AS[/color] account_number
       ,[COLOR=red]'FirstComp'[/color] [COLOR=blue]AS[/color] parent_account_number
       ,[COLOR=#FF00FF]PI[/color].PI_Paper [COLOR=blue]AS[/color] account_name
       ,[COLOR=red]''[/color] [COLOR=blue]AS[/color] policy_number
       ,[COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]Varchar[/color](1),[COLOR=red]'W'[/color]) [COLOR=blue]AS[/color] coverage_type
       ,[COLOR=red]''[/color] [COLOR=blue]AS[/color] effective_date
       ,[COLOR=red]''[/color] [COLOR=blue]AS[/color] termination_date
       ,[COLOR=#FF00FF]PI[/color].PI_Address [COLOR=blue]AS[/color] address_1
       ,[COLOR=#FF00FF]PI[/color].PI_City [COLOR=blue]AS[/color] city
       ,[COLOR=#FF00FF]PI[/color].PI_State [COLOR=blue]AS[/color] state
       ,[COLOR=#FF00FF]PI[/color].PI_PostalCode [COLOR=blue]as[/color] zip
[COLOR=blue]FROM[/color] dbo.Partner_Paper [COLOR=#FF00FF]Record[/color]
       [COLOR=blue]join[/color] pricing_PaperInformation_PI [COLOR=#FF00FF]PI[/color] [COLOR=blue]ON[/color] [COLOR=#FF00FF]Record[/color].PP_PaperID = [COLOR=#FF00FF]PI[/color].PI_ID
UNION
[COLOR=blue]SELECT[/color] [COLOR=red]'3'[/color] [COLOR=blue]AS[/color] account_level
       ,[COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]Varchar[/color](25),[COLOR=#FF00FF]Record[/color].IPI_ID) [COLOR=blue]AS[/color] account_number
       ,[COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]Varchar[/color](25),PP.PP_PaperID) [COLOR=blue]AS[/color] parent_account_number
       ,II.II_InsuredName [COLOR=blue]AS[/color] account_name
       ,[COLOR=#FF00FF]Record[/color].IPI_PolicyNumber [COLOR=blue]AS[/color] policy_number
       ,[COLOR=red]'W'[/color] [COLOR=blue]AS[/color] coverage_type
       ,[COLOR=#FF00FF]Record[/color].IPI_EffectiveDate [COLOR=blue]AS[/color] effective_date
       ,[COLOR=#FF00FF]Record[/color].IPI_ExpirationDate [COLOR=blue]AS[/color] termination_date
       ,[COLOR=red]''[/color] [COLOR=blue]AS[/color] address_1
       ,[COLOR=red]''[/color] [COLOR=blue]as[/color] city
       ,[COLOR=red]''[/color] [COLOR=blue]as[/color] state
       ,[COLOR=red]''[/color] [COLOR=blue]as[/color] zip
[COLOR=blue]FROM[/color] policy_InsuredPricingInfo_IPI [COLOR=#FF00FF]Record[/color]
       [COLOR=blue]JOIN[/color] Partner_Paper PP [COLOR=blue]ON[/color] [COLOR=#FF00FF]Record[/color].IPI_PaperID = PP.PP_PaperID
       [COLOR=blue]JOIN[/color] policy_InsuredInfo_II II [COLOR=blue]ON[/color] [COLOR=#FF00FF]Record[/color].IPI_II_ID = II.II_ID
       [COLOR=blue]JOIN[/color] policy_reference_Status_ST ST [COLOR=blue]ON[/color] [COLOR=#FF00FF]Record[/color].IPI_ST_ID = ST.ST_ID
[COLOR=blue]WHERE[/color] ST.ST_BrowsePolicies = 1
[COLOR=blue]FOR[/color] XML AUTO, ELEMENTS

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[monksnake]

I had just gotten back from lunch too. In the case that you are getting truncation problems on your results, and you're using Query Analyzer, make sure your

Maximum Characters Per Column is at least 2033

This may only apply when results are set to text.

<.

 

[SQLDenis]

Now if you had SQL 2005 you could expose a proc as a Webservice (WIN Server 2003 required)



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[ptheriault]

This is SQL 2005 and WIN Server 2003.

Please explain.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

read this

http://www.developer.com/db/article.php/3547866

http://msdn2.microsoft.com/en-us/library/ms345123.aspx

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[ptheriault]

Thank Denis.

- Paul

- If at first you don't succeed, find out if the loser gets anything.