Question

Sidaan on Wed, 26 Dec 2012 13:17:32


Hi

USE Northwind
SELECT * FROM  Customers Customer FOR XML AUTO, ROOT('Customers')

The above query doesn't produce an accurate XML file and because of that I am not able to load the file into a front end variable.

Could someone help please?


Sponsored



Replies

Olaf Helper on Wed, 26 Dec 2012 17:16:04


Hello Sidaan,

And what's in your opinion incorrect / inaccurate in the output?

Sidaan on Wed, 26 Dec 2012 17:42:28


Hi Olaf How you doing,

The output read by WordPad for

USE Northwind
SELECT * FROM  Customers Customer FOR XML AUTO, ROOT('Customers')

FOR XML AUTO, ELEMENTS

is

XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<Customer><CustomerID>ALFKI</CustomerID><CompanyName>Alfreds Futterkiste</CompanyName><ContactName>Maria Anders</ContactName><ContactTitle>Sales Representative</ContactTitle><Address>Obere Str. 57

'

'

</Phone></Customer><Customer><CustomerID>WHITC</CustomerID><CompanyName>White Clover Markets</CompanyName><ContactName>Karl Jablonski</ContactName><ContactTitle>Owner</ContactTitle><Address>

(91 row(s) affected)

When I tried to load the above into a VB.Net variable I get the error message

Data at the root level is invalid. Line 1, position 1.

Even if I remove 

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

still I get the error message

Name cannot begin with the '
' character, hexadecimal value 0x0D. Line 1, position 257.

Thanks.

wBob on Wed, 26 Dec 2012 18:22:37


SQL Server FOR XML AUTO providers perfectly valid XML.  0X0D is a carriage return.  There must be a problem with the way you are passing your XML to your program.

How exactly are you loading the xml into your variable?  You might need to post this on a VB.net group.

Sidaan on Wed, 26 Dec 2012 19:23:39


First I added the ROOT Element to the above bulky XML and then I checked it by removing the middle bulk of the XML to have

<ALLCUSTOMERS><Customer><CustomerID>ALFKI</CustomerID></Customer></ALLCUSTOMERS>

Then my loading works.

The problem is as said above the bulky XML above contains something invalid which stops loading.

Could you please try this on your machine?

On your SQL Server

USE Northwind
SELECT * FROM  Customers Customer 
FOR XML AUTO,  ROOT('ALLCUSTOMERS'),ELEMENTS

Execute the query to reach a file.

On your VB.Net declare the variable at the form level.

 

Dim customers As XElement = XElement.Load("Customers.xml")

Thanks.


Sidaan


Olaf Helper on Wed, 26 Dec 2012 19:55:38


is

XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<Customer><.....

Hello Sidaan,

You stored / exported the result the wrong way, incl column name, separator and so on. Execute the query with output to datagrid, then you get a hyperlink as result; click on it and you will see pretty fine well formed XML.

Sidaan on Wed, 26 Dec 2012 20:16:05


Hi Olaf,

Yes What I get in the Grid is a correct one but I did check that.

I check my code again, try another small table and come back.

Thanks. 

wBob on Wed, 26 Dec 2012 20:16:13


How are you exporting the XML to a file?

Maybe you are using sqlcmd which would include the headers and probably isn't suitable for exporting XML for use elsewhere.  Try bcp instead, eg

bcp "SELECT yourXML" queryout "c:\temp\temp.xml" -SyourServer -T -w -q

Sidaan on Thu, 27 Dec 2012 12:29:22


Hi

Could you please tell me how to use the code above? Is it something to be added to my SQL code in the SQL code editor?

I don't find any menu/submenu routes to control what gets exported. I just select file out 3 options for query output that are text, grid and file.

What you say is the problem actually.

First the query results exported to the text file contains a header but I could remove it using Query Options in Query menu.

Then the query results have a footer as well which I am not able to remove unless I manually remove it.

The third problem is more difficult; a few tags get broken on export. For example <phone>07272346666</phone> become <phone>07272346666</phon e> so the compiler reports error.

Thanks.


Sidaan


wBob on Thu, 27 Dec 2012 13:07:40


bcp is run from the command-line.   Press Start > Run > cmd

Change the 'yourServer' text in my bcp statement to be your SQL Server name ( including instance name ).

Sidaan on Thu, 27 Dec 2012 14:11:58


The cursor in my command prompt always starts after the file path C:\Users\Sidaan Var.. Se...>. Is it the one you mean? After typing bcp I typed my query within quotes. Then typed queryout. Then file path in quotes. Then with S I typed my server name; not just server name Then T-w-q No output went to the xml file.

Sidaan on Fri, 28 Dec 2012 18:29:46


Two problems.

The XML output to the file contains a line at the bottom saying so many rows were affected. It upsets the VB compiler.

Secondly the individual lines get broken when data is exported to a text/xml/rpt file. For example <phone>6666</phone> could be broken into

<phone>6666<'phon

e>

That to upsets the VB compiler. 

wBob on Sat, 29 Dec 2012 22:01:31


No output went to the xml file.
Are you sure?   Check the remote server.

Sidaan on Sun, 30 Dec 2012 17:32:58


Sure.

Error occurs because the code causes the cmd code complier to send instructions to connect to SQL 2005.

I am using SQL Server 2008 R2 Express.

And this is a little messy but please read.

Some time ago I tried to install SQL 2008 through internet. Could not. Referred to Google articles. One of them said it might be due to the already installed SQL version. I was not sure if SQL 2005 was actually installed. But certainly there were files named SQL something. I tried uninstalling them. The computer warned they were linked to some other files that had to be uninstalled before. Unfortunately before the warning I had already deleted more than just a few. I couldn't remember what and what then. So I left it like that. Since then the Microsoft update has been leaving lengthy error logs. Most of them are about SQL 2005.

Please suggest solutions.

wBob on Sun, 30 Dec 2012 20:35:25


Do clean uninstalls of SQL 2005 and SQL 2008 and start again:

Manually uninstall SQL 2005
http://support.microsoft.com/kb/909967

Manually uninstall SQL 2008
http://support.microsoft.com/kb/955499