MS SQL XML - sql as xml and bad xml format

Author

sql as xml and bad xml format

Robert Jenkin

2005-10-31, 8:24 pm

I have writen a c# program to load data from a sql table as xml and using
serialization populate classes with the data.

The problem, my question. It seams there is a data length limitation when
selecting sql as xml. My program selects 5 columns and crashes if I limit
the number of rows it works.

Does anyone have any idea as to a size limit with xml

Thanks

Bob

Michael Rys [MSFT]

2005-10-31, 8:24 pm

How do you get the XML back? If you are using SQL Server 2000, you will need
to use a specific API call to get the XML as a single stream back...

Best regards
Michael

"Robert Jenkin" < RobertJenkin@discuss
ions.microsoft.com> wrote in message
news:750A732E-932A-40C6-BDDC- 79EC7AE5AF1E@microso
ft.com...
>I have writen a c# program to load data from a sql table as xml and using
> serialization populate classes with the data.
>
> The problem, my question. It seams there is a data length limitation when
> selecting sql as xml. My program selects 5 columns and crashes if I limit
> the number of rows it works.
>
> Does anyone have any idea as to a size limit with xml
>
> Thanks
>
> Bob
>

Robert Jenkin

2005-10-31, 8:24 pm

the code is as follows

string sSQL = @"select * from sites FOR XML AUTO, ELEMENTS";
oSystemCommand = new SqlCommand(sSQL, SqlConnSystem);
oDataReader = oSystemCommand.ExecuteReader();
oDataReader.Read();
string sXML="";
if (oDataReader.HasRows)
{
sXML = "<data>" + oDataReader.GetString(0).Trim() + "</data>";
//sXML = oDataReader.GetString(0).Trim();
}
System.Console.WriteLine(sXML);
StringReader osr = new StringReader(sXML) ;
xmlser = new XmlSerializer(typeof
(data));
data oSites = (data)xmlser.Deserialize(osr);

bob jenkin (my first response was added as a new item, sorry)
"Michael Rys [MSFT]" wrote:

> How do you get the XML back? If you are using SQL Server 2000, you will need
> to use a specific API call to get the XML as a single stream back...
>
> Best regards
> Michael
>
> "Robert Jenkin" < RobertJenkin@discuss
ions.microsoft.com> wrote in message
> news:750A732E-932A-40C6-BDDC- 79EC7AE5AF1E@microso
ft.com...
>
>
>

Peter Flynn

2005-10-31, 8:24 pm

Robert Jenkin wrote:

> I have writen a c# program to load data from a sql table as xml and
> using serialization populate classes with the data.
>
> The problem, my question. It seams there is a data length limitation
> when
> selecting sql as xml. My program selects 5 columns and crashes if I
> limit the number of rows it works.
>
> Does anyone have any idea as to a size limit with xml

XML itself has no defined size limits, either on markup or on
data content (except for a minor historical oddity in its SGML
compliance mode which is probably irrelevant here).

Your software may have other limits, however.

///Peter
--
XML FAQ: http://xml.silmaril.ie/

Bertan ARI [MSFT]

2005-11-02, 3:23 am

Could you try getting xml by using ExecuteXmlReader? The following page has
an snippet code on how to do it:

http://msdn.microsoft.com/library/d...opic
.asp

Thanks
Bertan

--
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.

"Robert Jenkin" < RobertJenkin@discuss
ions.microsoft.com> wrote in message
news:98DF0890-B3C1-481C-85F5- 8F8F31F416B2@microso
ft.com...[color=darkred]
>
> the code is as follows
>
> string sSQL = @"select * from sites FOR XML AUTO, ELEMENTS";
> oSystemCommand = new SqlCommand(sSQL, SqlConnSystem);
> oDataReader = oSystemCommand.ExecuteReader();
> oDataReader.Read();
> string sXML="";
> if (oDataReader.HasRows)
> {
> sXML = "<data>" + oDataReader.GetString(0).Trim() + "</data>";
> //sXML = oDataReader.GetString(0).Trim();
> }
> System.Console.WriteLine(sXML);
> StringReader osr = new StringReader(sXML) ;
> xmlser = new XmlSerializer(typeof
(data));
> data oSites = (data)xmlser.Deserialize(osr);
>
> bob jenkin (my first response was added as a new item, sorry)
> "Michael Rys [MSFT]" wrote:
>

Sponsored Links