Query to XML File 1

ptheriault · Apr 27, 2007

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?

Code:

[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.

Jamfool · Apr 27, 2007

Difficult to tell without having the data to play with. In most cases it will be either a format issue (multiple root nodes) or a rogue character which xml dislikes.

Usually chars not in the ascii 128 range.

id suggest breaking the 3 queries into pieces and seeing if you can get it to output some valid xml. (perhaps disgard some column select at first too)

monksnake · Apr 27, 2007

Well since you are making an XML file, you have to have an XML version tag at the very top of the XML file. It'll look something like this:

Code:

<?xml version="1.0" ?>

I'm pretty sure I couldn't help you with the SQL, but I think you know enough that you can fix whatever's wrong as long as you know what it's supposed to look like.

Can you post a sample of the text you create that is supposed to be the XML file?

[monkey]

<.

ptheriault · Apr 27, 2007

Here is a sample of the XML file. It's the from the very top.

- <RecordSet>
- <Record>
<account_level>1</account_level>
<account_number>FirstComp</account_number>
<parent_account_number />
<account_name>FirstComp Insurance</account_name>
<policy_number />
<coverage_type>W</coverage_type>
<effective_date>1900-01-01T00:00:00</effective_date>
<termination_date>1900-01-01T00:00:00</termination_date>
<address_1 />
<city />
<state />
<zip />
</Record>
- <Record

- Paul

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

ptheriault · Apr 27, 2007

I did add this to the bottom

FOR XML AUTO, ELEMENTS,Root('RecordSet')

and it created recordset tags which did get rid of one error.

- Paul

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

SQLDenis · Apr 27, 2007

where is your root tag?

also this garbage gets outputted "XML_F52E2B61-18A1-11d1-B105-00805F49916B"

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

monksnake · Apr 27, 2007

Yeah, just insert that <?xml.. tag in the top and I'm not sure about the bottom of your file

Code:

- <Record

As long as that's just a continuation, everything looks fine.

[monkey]

<.

monksnake · Apr 27, 2007

where is your root tag?

His root tag is RecordSet. It doesn't have to be named root. Just as long as it encapsulates everything.

[monkey]

<.

SQLDenis · Apr 27, 2007

</Record>
- <Record

is this <Record

<Record>

or <RecordSet> ?

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

monksnake · Apr 27, 2007

I'll be dogged. You can scratch what I said about adding the <?xml tag, seems to work without it.

[monkey]

<.

monksnake · Apr 27, 2007

Don't worry Paul, I'M ON THE CASE!!!!

(be scared if I'm the only one) [cheers]

<.

ptheriault · Apr 27, 2007

Thanks Guys,
I just got back from Lunch.
As Denis pointed out.
I'm getting this Garbage at the top.

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

That's what is killing me right now.

- Paul

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

SQLDenis · Apr 27, 2007

That garbage is your alias

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

gmmastros · Apr 27, 2007

This won't be too helpful (for the immediate task at hand), but I suggest you use UNION ALL instead of union since each query will be distinct anyway.

It'll probably speed up the query a bit.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

gmmastros · Apr 27, 2007

Paul,

I think that 'bad text' is the column header.

Try this...

In QA, click Tools -> Options
Click 'Results' tab
Unselect 'Print column headers'

Also, make sure you have 'SET NOCOUNT ON' at the top of the procedure.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

gmmastros · Apr 27, 2007

In SQL 2005 (Management Studio)....

Click Tools -> Options
Expand 'Query Results' -> 'SQL Server' -> 'Results To Text'
Unselect 'Include column headers in the result set'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

ptheriault · Apr 27, 2007

Thanks George,
I change the query to UNION ALL. Is is faster.

But I still this at the top of my xml file.

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

- Paul

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

gmmastros · Apr 27, 2007

Even after changing the 'print column headers'?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

ptheriault · Apr 27, 2007

I changed the wrong one.
I change Results to grid instead of result to text.
So I don't get that anymore after changing the results to text.

Thanks for that.

- Paul

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

ptheriault · Apr 27, 2007

Thanks Guys.

I think I've got it working.

- Paul

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Query to XML File 1

IS-IT--Management

IS-IT--Management

Programmer

IS-IT--Management

IS-IT--Management

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

IS-IT--Management

Programmer

Programmer

Programmer

Programmer

IS-IT--Management

Programmer

IS-IT--Management

IS-IT--Management

Similar threads

Log in

Part and Inventory Search

Sponsor