473,574 Members | 2,914 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

XMLSerializer and SQL Server

Hello,

I'm trying to use the FOR XML EXPLICIT clause with SQL
Server to deserialize data from my database into a
strongly-typed collection object that I will use
throughout my application. I initially tested my design
by building a collection in code and then serializing it
to/from an XML file, which worked fine. However, I have
hit a brick wall trying to restore the data from SQL
Server. I originally had my collection and object
classes just marked as [Serializable()] without any of
the XML Serialization attributes; however, replacing
the "standard" serialization attributes with XmlRoot and
XmlAttributes (on properties) has not yielded any
success. The error I am receiving is as follows when
marked as Serializable:

Unhandled Exception: System.InvalidO perationExcepti on:
There is an error in XML
document (1, 2). ---> System.InvalidO perationExcepti on:
<Categories xmlns=''> wa
s not expected.
at
Microsoft.Xml.S erialization.Ge neratedAssembly .XmlSerializa
tionReader1.Rea d
4_ArrayOfCatego ry()
--- End of inner exception stack trace ---
at System.Xml.Seri alization.XmlSe rializer.Deseri alize
(XmlReader xmlReader, St
ring encodingStyle)
at System.Xml.Seri alization.XmlSe rializer.Deseri alize
(XmlReader xmlReader)
at PubsData.Tester .main() in F:\Global
Documents\Visua l Studio .NET Projects\
PHMCPubs\PubsDa ta\Class1.vb:li ne 64

<Categories> is my root element, followed by an arbitrary
number of <Category attrib1="xyz" attrib2="123".. ./>
elements.

I appreciate any comments or suggestions that anyone may
have.

Thanks,

Harris
Nov 11 '05 #1
8 3555
Have you read this article?
http://msdn.microsoft.com/library/de...ml04212003.asp

Thanks. Mark
PM - System.Xml

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Harris Boyce" <hb****@thedotw orks.com> wrote in message
news:06******** *************** *****@phx.gbl.. .
Hello,

I'm trying to use the FOR XML EXPLICIT clause with SQL
Server to deserialize data from my database into a
strongly-typed collection object that I will use
throughout my application. I initially tested my design
by building a collection in code and then serializing it
to/from an XML file, which worked fine. However, I have
hit a brick wall trying to restore the data from SQL
Server. I originally had my collection and object
classes just marked as [Serializable()] without any of
the XML Serialization attributes; however, replacing
the "standard" serialization attributes with XmlRoot and
XmlAttributes (on properties) has not yielded any
success. The error I am receiving is as follows when
marked as Serializable:

Unhandled Exception: System.InvalidO perationExcepti on:
There is an error in XML
document (1, 2). ---> System.InvalidO perationExcepti on:
<Categories xmlns=''> wa
s not expected.
at
Microsoft.Xml.S erialization.Ge neratedAssembly .XmlSerializa
tionReader1.Rea d
4_ArrayOfCatego ry()
--- End of inner exception stack trace ---
at System.Xml.Seri alization.XmlSe rializer.Deseri alize
(XmlReader xmlReader, St
ring encodingStyle)
at System.Xml.Seri alization.XmlSe rializer.Deseri alize
(XmlReader xmlReader)
at PubsData.Tester .main() in F:\Global
Documents\Visua l Studio .NET Projects\
PHMCPubs\PubsDa ta\Class1.vb:li ne 64

<Categories> is my root element, followed by an arbitrary
number of <Category attrib1="xyz" attrib2="123".. ./>
elements.

I appreciate any comments or suggestions that anyone may
have.

Thanks,

Harris

Nov 11 '05 #2
Harris,

can you post the code you use to deserialize and the collection/classes you
deserialize into?

--
HTH
Christoph Schittko [MVP]
Software Architect, .NET Mentor

"Harris Boyce" <hb****@thedotw orks.com> wrote in message
news:06******** *************** *****@phx.gbl.. .
Hello,

I'm trying to use the FOR XML EXPLICIT clause with SQL
Server to deserialize data from my database into a
strongly-typed collection object that I will use
throughout my application. I initially tested my design
by building a collection in code and then serializing it
to/from an XML file, which worked fine. However, I have
hit a brick wall trying to restore the data from SQL
Server. I originally had my collection and object
classes just marked as [Serializable()] without any of
the XML Serialization attributes; however, replacing
the "standard" serialization attributes with XmlRoot and
XmlAttributes (on properties) has not yielded any
success. The error I am receiving is as follows when
marked as Serializable:

Unhandled Exception: System.InvalidO perationExcepti on:
There is an error in XML
document (1, 2). ---> System.InvalidO perationExcepti on:
<Categories xmlns=''> wa
s not expected.
at
Microsoft.Xml.S erialization.Ge neratedAssembly .XmlSerializa
tionReader1.Rea d
4_ArrayOfCatego ry()
--- End of inner exception stack trace ---
at System.Xml.Seri alization.XmlSe rializer.Deseri alize
(XmlReader xmlReader, St
ring encodingStyle)
at System.Xml.Seri alization.XmlSe rializer.Deseri alize
(XmlReader xmlReader)
at PubsData.Tester .main() in F:\Global
Documents\Visua l Studio .NET Projects\
PHMCPubs\PubsDa ta\Class1.vb:li ne 64

<Categories> is my root element, followed by an arbitrary
number of <Category attrib1="xyz" attrib2="123".. ./>
elements.

I appreciate any comments or suggestions that anyone may
have.

Thanks,

Harris

Nov 11 '05 #3
Christoph,

Thanks for your reply; below is all of the code -
collection, IEnumerator and CollectionObjec t that I am
using. Originally, I had tried to generate the class(es)
using XSD.exe from the XDR using XMLDATA in the stored
procedure, which created the comments you see (see my
reply to the SQL Dev Team post for the the SP code). As
a note, please keep in mind that I am using
SqlCommand.Exec uteXMLReader and not
SqlXmlCommand.E xecuteXmlReader to get the data from SQL
Server; I'm trying to make the application as dependency-
free as possible.

Thanks for your time,

Harris

-- CODE: --

Option Strict On
Option Explicit On

Imports System
Imports System.Collecti ons
Imports System.Xml.Seri alization

'
'This source code was auto-generated by xsd,
Version=1.1.432 2.573.
'

'<remarks/>
'<System.Xml.Se rialization.Xml RootAttribute(" Categories",
[Namespace]:="", IsNullable:=Fal se)> _
<Serializable() > _
Public Class Categories
Inherits CollectionBase

'<remarks/>
'<System.Xml.Se rialization.Xml ElementAttribut e
("Category",
Form:=System.Xm l.Schema.XmlSch emaForm.Unquali fied)> _
'Public Items() As Category

Public Sub New()
MyBase.New()
End Sub

Public Sub New(ByVal NewCategories As Categories)
MyBase.New()
AddRange(NewCat egories)
End Sub

Public Sub New(ByVal arrCategories() As Category)
MyBase.New()
AddRange(arrCat egories)
End Sub

Default Public Property Item(ByVal index As Integer)
As Category

Get
Return DirectCast(Me.L ist.Item(index) ,
Category)
End Get

Set(ByVal Value As Category)
Me.List.Item(in dex) = Value
End Set

End Property

Public Function Add(ByVal aCategory As Category) As
Integer
Return Me.List.Add(aCa tegory)
End Function

Public Overloads Sub AddRange(ByVal values() As
Category)

Try
For Each cat As Category In values
Me.Add(cat)
Next

Catch ex As Exception
'umm...??
End Try

End Sub

Public Overloads Sub AddRange(ByVal values As
Categories)

Try

For Each cat As Category In values
Me.Add(cat)
Next

Catch ex As Exception
'umm...???
End Try

End Sub

Public Sub CopyTo(ByVal catArray() As Category, ByVal
index As Integer)
Me.List.CopyTo( catArray, index)
End Sub

Public Function Contains(ByVal value As Category) As
Boolean
Return MyBase.List.Con tains(value)
End Function

Public Function IndexOf(ByVal cat As Category) As
Integer
Return Me.List.IndexOf (cat)
End Function

Public Sub Insert(ByVal index As Integer, ByVal value
As Category)
Me.List.Insert( index, value)
End Sub

Public Sub Remove(ByVal value As Category)
Me.List.Remove( value)
End Sub

Public Shadows Function GetEnumerator() As
CategoryEnumera tor
Return New CategoryEnumera tor(Me)
End Function

End Class

Public Class CategoryEnumera tor
Inherits Object
Implements IEnumerator

Private _baseEnumerator As IEnumerator
Private _tempEnumerable As IEnumerable

Public Sub New(ByVal mappings As Categories)

MyBase.new()
Me._tempEnumera ble = DirectCast(mapp ings,
IEnumerable)
Me._baseEnumera tor =
Me._tempEnumera ble.GetEnumerat or

End Sub

Public ReadOnly Property IEnumerator_Cur rent() As
Object Implements System.Collecti ons.IEnumerator .Current
Get
Return _baseEnumerator .Current
End Get
End Property

Public Function IEnumerator_Mov eNext() As Boolean
Implements System.Collecti ons.IEnumerator .MoveNext
Return _baseEnumerator .MoveNext()
End Function

Public Sub IEnumerator_Res et() Implements
System.Collecti ons.IEnumerator .Reset
_baseEnumerator .Reset()
End Sub

Public ReadOnly Property Current() As Category
Get
Return DirectCast(_bas eEnumerator.Cur rent,
Category)
End Get
End Property

Public Function MoveNext() As Boolean
Return _baseEnumerator .MoveNext()
End Function

Public Sub Reset()
_baseEnumerator .Reset()
End Sub

End Class
'<remarks/>
'<XmlRoot("Cate gory", [Namespace]:="", IsNullable:=Fal se)
_ <Serializable() > _
Public Class Category

'<remarks/>
Private _CategoryID As Integer
'<System.Xml.Se rialization.Xml AttributeAttrib ute()> _
Public Property CategoryID() As Integer

Get
Return _CategoryID
End Get

Set(ByVal Value As Integer)
_CategoryID = Value
End Set

End Property

'<remarks/>
Private _CategoryName As String
'<System.Xml.Se rialization.Xml AttributeAttrib ute()> _
Public Property CategoryName() As String

Get
Return _CategoryName
End Get

Set(ByVal Value As String)
_CategoryName = Value
End Set

End Property

'<remarks/>
Private _DisplayOrder As Integer
'<System.Xml.Se rialization.Xml AttributeAttrib ute()> _
Public Property DisplayOrder() As Integer

Get
Return _DisplayOrder
End Get

Set(ByVal Value As Integer)
_DisplayOrder = Value
End Set

End Property

End Class

-----Original Message-----
Harris,

can you post the code you use to deserialize and the collection/classes youdeserialize into?

--
HTH
Christoph Schittko [MVP]
Software Architect, .NET Mentor

"Harris Boyce" <hb****@thedotw orks.com> wrote in message
news:06******* *************** ******@phx.gbl. ..
Hello,

I'm trying to use the FOR XML EXPLICIT clause with SQL
Server to deserialize data from my database into a
strongly-typed collection object that I will use
throughout my application. I initially tested my design by building a collection in code and then serializing it to/from an XML file, which worked fine. However, I have hit a brick wall trying to restore the data from SQL
Server. I originally had my collection and object
classes just marked as [Serializable()] without any of
the XML Serialization attributes; however, replacing
the "standard" serialization attributes with XmlRoot and XmlAttributes (on properties) has not yielded any
success. The error I am receiving is as follows when
marked as Serializable:

Unhandled Exception: System.InvalidO perationExcepti on:
There is an error in XML
document (1, 2). ---> System.InvalidO perationExcepti on:
<Categories xmlns=''> wa
s not expected.
at
Microsoft.Xml.S erialization.Ge neratedAssembly .XmlSerializa tionReader1.Rea d
4_ArrayOfCatego ry()
--- End of inner exception stack trace ---
at System.Xml.Seri alization.XmlSe rializer.Deseri alize (XmlReader xmlReader, St
ring encodingStyle)
at System.Xml.Seri alization.XmlSe rializer.Deseri alize (XmlReader xmlReader)
at PubsData.Tester .main() in F:\Global
Documents\Visua l Studio .NET Projects\
PHMCPubs\PubsDa ta\Class1.vb:li ne 64

<Categories> is my root element, followed by an arbitrary number of <Category attrib1="xyz" attrib2="123".. ./>
elements.

I appreciate any comments or suggestions that anyone may have.

Thanks,

Harris

.

Nov 11 '05 #4
Harris,

It appears you are selecting the something from the database which defines
the default namespace of the element, i.e. it creates an xmlns attribute
(this may just appear because of line breaks in the posting)
None of your classes identify any namespace for the XML nodes they map to,
so I suspect your problem is somewhat related to that. The exception message
complains also complains about encoutering a namespace declaration when
reading the returned XML, so there is something going on here.

However, an empty namespace declaration like your exception message cites
should not create a problem, and it didn't when I verified it.

Have you actually made sure that the Xml you get back from your stored
procedure matches the test data you had, complete with namespace
declarations, etc? You could, for example, just write the output from the
ExecuteXmlReade r to the console or a file by using the WriteNode method of
an XmlTextWriter, such as:

XmlTextWriter writer = new XmlTextWriter( Console.Out );
writer.WriteSta rtElement( "MyRoot" );
writer.WriteNod e( cmd.ExecuteXmlR eader() );
writer.WriteEnd Element();

--
HTH
Christoph Schittko [MVP]
Software Architect, .NET Mentor

"Harris Boyce" <hb****@thedotw orks.com> wrote in message
news:26******** *************** *****@phx.gbl.. .
Mark,

Thanks for your reply. I looked over the article before
I began working on this project and took notes. However,
I'm trying not to couple the application so tightly,
thus, I'm looking to avoid using extra dependencies like
the SQLXML Managed Classes. With that said, I attempt to
deserialize the XML by calling
SqlCommand.Exec uteXmlReader. I've included my stored
procedure code below; the second, commented SELECT was a
test without creating a root element inside the SP,
hoping that the XmlSerializer would see the XML as an
Array of Category objects and then I could just load the
array into my strongly-typed collection. I also posted
the code for the collection, IEnumerator, and "collection-
object" classes in my reply to Christoph if you would
like to take a gander at those as well.

Thanks for your time and ideas,

Harris

-- SP CODE: (CREATE PROCEDURE omitted) --

SELECT 1 AS tag,
null AS parent,
NULL AS
[Categories!1!!e lement],
--'http://tdw/' AS
[Categories!1!xm lns],
NULL AS [Category!
2!CategoryID],
NULL AS [Category!
2!CategoryName],
null AS [Category!
2!DisplayOrder]

UNION ALL

SELECT 2 AS tag,
1 AS parent,
NULL AS
[Categories!1!!e lement],
--NULL AS
[Categories!1!xm lns],
-1 AS [Category!
2!CategoryID],
'Select Category ' AS
[Category!2!Cate goryName],
-1 AS [Category!
2!DisplayOrder]
UNION ALL

SELECT 2 AS tag,
1 AS parent,
NULL AS
[Categories!1!!e lement],
--NULL AS
[Categories!1!xm lns],
CategoryID AS [Category!
2!CategoryID],
[Name] AS [Category!
2!CategoryName],
DisplayOrder AS [Category!
2!DisplayOrder]
FROM categories

ORDER BY [Category!2!Disp layOrder]
--
-- SELECT 1 AS
tag,
-- NULL AS parent,
-- -- NULL AS
[Categories!1!!e lement],
-- --NULL AS
[Categories!1!xm lns],
-- -1 AS [Category!
1!CategoryID],
-- 'Select Category ' AS
[Category!1!Cate goryName],
-- -1 AS [Category!
1!DisplayOrder]
--
--
-- UNION ALL
--
-- SELECT 1 AS
tag,
-- NULL AS parent,
-- -- NULL AS
[Categories!1!!e lement],
-- --NULL AS
[Categories!1!xm lns],
-- CategoryID AS [Category!
1!CategoryID],
-- ltrim(rtrim([Name])) AS [Category!
1!CategoryName],
-- DisplayOrder AS [Category!
1!DisplayOrder]
-- FROM categories
--
-- ORDER BY [Category!1!Disp layOrder]

FOR XML EXPLICIT
-----Original Message-----
Have you read this article?
http://msdn.microsoft.com/library/default.asp?

url=/library/en-us/dnexxml/html/xml04212003.asp

Thanks. Mark
PM - System.Xml

--
This posting is provided "AS IS" with no warranties, and

confers no rights.
Use of included script samples are subject to the terms

specified at
http://www.microsoft.com/info/cpyright.htm.
"Harris Boyce" <hb****@thedotw orks.com> wrote in message
news:06******* *************** ******@phx.gbl. ..
Hello,

I'm trying to use the FOR XML EXPLICIT clause with SQL
Server to deserialize data from my database into a
strongly-typed collection object that I will use
throughout my application. I initially tested my design by building a collection in code and then serializing it to/from an XML file, which worked fine. However, I have hit a brick wall trying to restore the data from SQL
Server. I originally had my collection and object
classes just marked as [Serializable()] without any of
the XML Serialization attributes; however, replacing
the "standard" serialization attributes with XmlRoot and XmlAttributes (on properties) has not yielded any
success. The error I am receiving is as follows when
marked as Serializable:

Unhandled Exception: System.InvalidO perationExcepti on:
There is an error in XML
document (1, 2). ---> System.InvalidO perationExcepti on:
<Categories xmlns=''> wa
s not expected.
at
Microsoft.Xml.S erialization.Ge neratedAssembly .XmlSerializa tionReader1.Rea d
4_ArrayOfCatego ry()
--- End of inner exception stack trace ---
at System.Xml.Seri alization.XmlSe rializer.Deseri alize (XmlReader xmlReader, St
ring encodingStyle)
at System.Xml.Seri alization.XmlSe rializer.Deseri alize (XmlReader xmlReader)
at PubsData.Tester .main() in F:\Global
Documents\Visua l Studio .NET Projects\
PHMCPubs\PubsDa ta\Class1.vb:li ne 64

<Categories> is my root element, followed by an arbitrary number of <Category attrib1="xyz" attrib2="123".. ./>
elements.

I appreciate any comments or suggestions that anyone may have.

Thanks,

Harris

.

Nov 11 '05 #5
Christoph,

OK, I have some answers...I think...I took a look at the
file that I created when I manually built a collect using
a SQLDataReader and just manually created Category
objects, added each to an instance of a Categories
collection, and serialized the data out to an XML file.
The problem is that, I think, since the classes are
marked as Serializable() and are not using the Xml*
serializtion attributes, my root node in the serialized
XML file is ArrayOfCategory and not Categories like my
FOR XML stored procedure is creating...So now I am
wondering what XML attributes I need to mark my objects
with so that I am able to serialize them the way I would
like or if I should just throw in the towel and try to
conform to the schema .NET seems to building for me.

I've included examples of both documents so you can see
what I'm referring to. Thanks so much for your time and
thoughts. I appreciate it.

H

-- -=-= BEGIN Serializable() DOCUMENT =-=- --
<ArrayOfCategor y
xmlns:xsd="http ://www.w3.org/2001/XMLSchema"
xmlns:xsi="http ://www.w3.org/2001/XMLSchema-instance">
<Category>
<CategoryID>3 </CategoryID>
<CategoryName>A rchaeology and Native
Americans</CategoryName>
<DisplayOrder>0 </DisplayOrder>
</Category>
<Category>
<CategoryID>4 </CategoryID>
<CategoryName>A rchitecture and Historic
Preservation</CategoryName>
<DisplayOrder>0 </DisplayOrder>
</Category>
Nov 11 '05 #6
Christoph,

In a way, please disregard my previous message. I sort
of figured it out...I marked all the properties on my
Category class with XmlAttribute and marked the class
itself with XmlRootAttribut e(ElementName:= "Category") . I
then kept the collection class as Serializable; however,
when I created an instance of the XmlSerializer class, I
used the following constructor implementation:

XmlSerializer s = new XmlSerializer(t ypeof(Categorie s),
new XmlRootAttribut e("Category") );

With those changes, I was able to serialize my data in
the format I was looking for...Problem is, I'm not sure
why it worked. I love it when that happens, don't you?

Have any ideas as to why?

Again, I can't thank you enough for all your time.

Harris

-----Original Message-----
Harris,

It appears you are selecting the something from the database which definesthe default namespace of the element, i.e. it creates an xmlns attribute(this may just appear because of line breaks in the posting)None of your classes identify any namespace for the XML nodes they map to,so I suspect your problem is somewhat related to that. The exception messagecomplains also complains about encoutering a namespace declaration whenreading the returned XML, so there is something going on here.
However, an empty namespace declaration like your exception message citesshould not create a problem, and it didn't when I verified it.
Have you actually made sure that the Xml you get back from your storedprocedure matches the test data you had, complete with namespacedeclarations , etc? You could, for example, just write the output from theExecuteXmlRead er to the console or a file by using the WriteNode method ofan XmlTextWriter, such as:

XmlTextWrite r writer = new XmlTextWriter( Console.Out );
writer.WriteSt artElement( "MyRoot" );
writer.WriteNo de( cmd.ExecuteXmlR eader() );
writer.WriteEn dElement();

--
HTH
Christoph Schittko [MVP]
Software Architect, .NET Mentor

"Harris Boyce" <hb****@thedotw orks.com> wrote in message
news:26******* *************** ******@phx.gbl. ..
Mark,

Thanks for your reply. I looked over the article before
I began working on this project and took notes. However, I'm trying not to couple the application so tightly,
thus, I'm looking to avoid using extra dependencies like the SQLXML Managed Classes. With that said, I attempt to deserialize the XML by calling
SqlCommand.Exec uteXmlReader. I've included my stored
procedure code below; the second, commented SELECT was a test without creating a root element inside the SP,
hoping that the XmlSerializer would see the XML as an
Array of Category objects and then I could just load the array into my strongly-typed collection. I also posted
the code for the collection, IEnumerator, and "collection- object" classes in my reply to Christoph if you would
like to take a gander at those as well.

Thanks for your time and ideas,

Harris

-- SP CODE: (CREATE PROCEDURE omitted) --

SELECT 1 AS tag,
null AS parent,
NULL AS
[Categories!1!!e lement],
--'http://tdw/' AS
[Categories!1!xm lns],
NULL AS [Category!
2!CategoryID],
NULL AS [Category!
2!CategoryName],
null AS [Category!
2!DisplayOrder]

UNION ALL

SELECT 2 AS tag,
1 AS parent,
NULL AS
[Categories!1!!e lement],
--NULL AS
[Categories!1!xm lns],
-1 AS [Category!
2!CategoryID],
'Select Category ' AS
[Category!2!Cate goryName],
-1 AS [Category!
2!DisplayOrder]
UNION ALL

SELECT 2 AS tag,
1 AS parent,
NULL AS
[Categories!1!!e lement],
--NULL AS
[Categories!1!xm lns],
CategoryID AS [Category!
2!CategoryID],
[Name] AS [Category!
2!CategoryName],
DisplayOrder AS [Category!
2!DisplayOrder]
FROM categories

ORDER BY [Category!2!Disp layOrder]
--
-- SELECT 1 AS
tag,
-- NULL AS parent,
-- -- NULL AS
[Categories!1!!e lement],
-- --NULL AS
[Categories!1!xm lns],
-- -1 AS [Category!
1!CategoryID],
-- 'Select Category ' AS
[Category!1!Cate goryName],
-- -1 AS [Category!
1!DisplayOrder]
--
--
-- UNION ALL
--
-- SELECT 1 AS
tag,
-- NULL AS parent,
-- -- NULL AS
[Categories!1!!e lement],
-- --NULL AS
[Categories!1!xm lns],
-- CategoryID AS [Category!
1!CategoryID],
-- ltrim(rtrim([Name])) AS [Category!
1!CategoryName],
-- DisplayOrder AS [Category!
1!DisplayOrder]
-- FROM categories
--
-- ORDER BY [Category!1!Disp layOrder]

FOR XML EXPLICIT
>-----Original Message-----
>Have you read this article?
>http://msdn.microsoft.com/library/default.asp?

url=/library/en-us/dnexxml/html/xml04212003.asp
>
>Thanks. Mark
>PM - System.Xml
>
>--
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Use of included script samples are subject to the
terms specified at
>http://www.microsoft.com/info/cpyright.htm.
>"Harris Boyce" <hb****@thedotw orks.com> wrote in
message >news:06******* *************** ******@phx.gbl. ..
>> Hello,
>>
>> I'm trying to use the FOR XML EXPLICIT clause with SQL >> Server to deserialize data from my database into a
>> strongly-typed collection object that I will use
>> throughout my application. I initially tested my

design
>> by building a collection in code and then serializing it
>> to/from an XML file, which worked fine. However, I

have
>> hit a brick wall trying to restore the data from SQL
>> Server. I originally had my collection and object
>> classes just marked as [Serializable()] without any

of >> the XML Serialization attributes; however, replacing
>> the "standard" serialization attributes with XmlRoot

and
>> XmlAttributes (on properties) has not yielded any
>> success. The error I am receiving is as follows when >> marked as Serializable:
>>
>> Unhandled Exception: System.InvalidO perationExcepti on: >> There is an error in XML
>> document (1, 2). ---> System.InvalidO perationExcepti on: >> <Categories xmlns=''> wa
>> s not expected.
>> at
>>

Microsoft.Xml.S erialization.Ge neratedAssembly .XmlSerializa
>> tionReader1.Rea d
>> 4_ArrayOfCatego ry()
>> --- End of inner exception stack trace ---
>> at

System.Xml.Seri alization.XmlSe rializer.Deseri alize
>> (XmlReader xmlReader, St
>> ring encodingStyle)
>> at

System.Xml.Seri alization.XmlSe rializer.Deseri alize
>> (XmlReader xmlReader)
>> at PubsData.Tester .main() in F:\Global
>> Documents\Visua l Studio .NET Projects\
>> PHMCPubs\PubsDa ta\Class1.vb:li ne 64
>>
>> <Categories> is my root element, followed by an

arbitrary
>> number of <Category attrib1="xyz" attrib2="123".. ./>
>> elements.
>>
>> I appreciate any comments or suggestions that anyone

may
>> have.
>>
>> Thanks,
>>
>> Harris
>>
>>
>
>
>.
>

.

Nov 11 '05 #7
Now that you mention it ...

I vaguely remember this coming up before, but I don't think it was ever
confirmed as a bug ... even though I believe it is ;)

--
HTH
Christoph Schittko [MVP]
Software Architect, .NET Mentor

"Harris Boyce" <hb****@thedotw orks.com> wrote in message
news:4f******** *************** *****@phx.gbl.. .
Christoph,

In a way, please disregard my previous message. I sort
of figured it out...I marked all the properties on my
Category class with XmlAttribute and marked the class
itself with XmlRootAttribut e(ElementName:= "Category") . I
then kept the collection class as Serializable; however,
when I created an instance of the XmlSerializer class, I
used the following constructor implementation:

XmlSerializer s = new XmlSerializer(t ypeof(Categorie s),
new XmlRootAttribut e("Category") );

With those changes, I was able to serialize my data in
the format I was looking for...Problem is, I'm not sure
why it worked. I love it when that happens, don't you?

Have any ideas as to why?

Again, I can't thank you enough for all your time.

Harris

-----Original Message-----
Harris,

It appears you are selecting the something from the

database which defines
the default namespace of the element, i.e. it creates an

xmlns attribute
(this may just appear because of line breaks in the

posting)
None of your classes identify any namespace for the XML

nodes they map to,
so I suspect your problem is somewhat related to that.

The exception message
complains also complains about encoutering a namespace

declaration when
reading the returned XML, so there is something going on

here.

However, an empty namespace declaration like your

exception message cites
should not create a problem, and it didn't when I

verified it.

Have you actually made sure that the Xml you get back

from your stored
procedure matches the test data you had, complete with

namespace
declarations , etc? You could, for example, just write

the output from the
ExecuteXmlRead er to the console or a file by using the

WriteNode method of
an XmlTextWriter, such as:

XmlTextWrite r writer = new XmlTextWriter( Console.Out );
writer.WriteSt artElement( "MyRoot" );
writer.WriteNo de( cmd.ExecuteXmlR eader() );
writer.WriteEn dElement();

--
HTH
Christoph Schittko [MVP]
Software Architect, .NET Mentor

"Harris Boyce" <hb****@thedotw orks.com> wrote in message
news:26******* *************** ******@phx.gbl. ..
Mark,

Thanks for your reply. I looked over the article before I began working on this project and took notes. However, I'm trying not to couple the application so tightly,
thus, I'm looking to avoid using extra dependencies like the SQLXML Managed Classes. With that said, I attempt to deserialize the XML by calling
SqlCommand.Exec uteXmlReader. I've included my stored
procedure code below; the second, commented SELECT was a test without creating a root element inside the SP,
hoping that the XmlSerializer would see the XML as an
Array of Category objects and then I could just load the array into my strongly-typed collection. I also posted
the code for the collection, IEnumerator, and "collection- object" classes in my reply to Christoph if you would
like to take a gander at those as well.

Thanks for your time and ideas,

Harris

-- SP CODE: (CREATE PROCEDURE omitted) --

SELECT 1 AS tag,
null AS parent,
NULL AS
[Categories!1!!e lement],
--'http://tdw/' AS
[Categories!1!xm lns],
NULL AS [Category!
2!CategoryID],
NULL AS [Category!
2!CategoryName],
null AS [Category!
2!DisplayOrder]

UNION ALL

SELECT 2 AS tag,
1 AS parent,
NULL AS
[Categories!1!!e lement],
--NULL AS
[Categories!1!xm lns],
-1 AS [Category!
2!CategoryID],
'Select Category ' AS
[Category!2!Cate goryName],
-1 AS [Category!
2!DisplayOrder]
UNION ALL

SELECT 2 AS tag,
1 AS parent,
NULL AS
[Categories!1!!e lement],
--NULL AS
[Categories!1!xm lns],
CategoryID AS [Category!
2!CategoryID],
[Name] AS [Category!
2!CategoryName],
DisplayOrder AS [Category!
2!DisplayOrder]
FROM categories

ORDER BY [Category!2!Disp layOrder]
--
-- SELECT 1 AS
tag,
-- NULL AS parent,
-- -- NULL AS
[Categories!1!!e lement],
-- --NULL AS
[Categories!1!xm lns],
-- -1 AS [Category!
1!CategoryID],
-- 'Select Category ' AS
[Category!1!Cate goryName],
-- -1 AS [Category!
1!DisplayOrder]
--
--
-- UNION ALL
--
-- SELECT 1 AS
tag,
-- NULL AS parent,
-- -- NULL AS
[Categories!1!!e lement],
-- --NULL AS
[Categories!1!xm lns],
-- CategoryID AS [Category!
1!CategoryID],
-- ltrim(rtrim([Name])) AS [Category!
1!CategoryName],
-- DisplayOrder AS [Category!
1!DisplayOrder]
-- FROM categories
--
-- ORDER BY [Category!1!Disp layOrder]

FOR XML EXPLICIT

>-----Original Message-----
>Have you read this article?
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnexxml/html/xml04212003.asp
>
>Thanks. Mark
>PM - System.Xml
>
>--
>This posting is provided "AS IS" with no warranties, and confers no rights.
>Use of included script samples are subject to the terms specified at
>http://www.microsoft.com/info/cpyright.htm.
>"Harris Boyce" <hb****@thedotw orks.com> wrote in message >news:06******* *************** ******@phx.gbl. ..
>> Hello,
>>
>> I'm trying to use the FOR XML EXPLICIT clause with SQL >> Server to deserialize data from my database into a
>> strongly-typed collection object that I will use
>> throughout my application. I initially tested my
design
>> by building a collection in code and then serializing it
>> to/from an XML file, which worked fine. However, I
have
>> hit a brick wall trying to restore the data from SQL
>> Server. I originally had my collection and object
>> classes just marked as [Serializable()] without any of >> the XML Serialization attributes; however, replacing
>> the "standard" serialization attributes with XmlRoot
and
>> XmlAttributes (on properties) has not yielded any
>> success. The error I am receiving is as follows when >> marked as Serializable:
>>
>> Unhandled Exception: System.InvalidO perationExcepti on: >> There is an error in XML
>> document (1, 2). ---> System.InvalidO perationExcepti on: >> <Categories xmlns=''> wa
>> s not expected.
>> at
>>
Microsoft.Xml.S erialization.Ge neratedAssembly .XmlSerializa >> tionReader1.Rea d
>> 4_ArrayOfCatego ry()
>> --- End of inner exception stack trace ---
>> at
System.Xml.Seri alization.XmlSe rializer.Deseri alize
>> (XmlReader xmlReader, St
>> ring encodingStyle)
>> at
System.Xml.Seri alization.XmlSe rializer.Deseri alize
>> (XmlReader xmlReader)
>> at PubsData.Tester .main() in F:\Global
>> Documents\Visua l Studio .NET Projects\
>> PHMCPubs\PubsDa ta\Class1.vb:li ne 64
>>
>> <Categories> is my root element, followed by an
arbitrary
>> number of <Category attrib1="xyz" attrib2="123".. ./>
>> elements.
>>
>> I appreciate any comments or suggestions that anyone
may
>> have.
>>
>> Thanks,
>>
>> Harris
>>
>>
>
>
>.
>

.

Nov 11 '05 #8
No, I believe it is supposed to work with the XmlRoot arrtibute attached to
the collection. You do need the XmlAttribute on your fields though, which
were commented out in the sample you posted.

I think you're approach is fine. I would consider Updategrams rather than
OPENXML if you are looking for a good way to store XML in SQL Server. They
have some bugs and limitations and get a little clunky, but most of the bugs
were fixed in SQLXML 3SP2. Send me an email and I might be able to send you
some stuff on updategrams.

--
HTH
Christoph Schittko [MVP]
Software Architect, .NET Mentor

"Harris Boyce" <hb****@thedotw orks.com> wrote in message
news:47******** *************** *****@phx.gbl.. .

So what you're saying is that the code really shouldn't
work?? Excellent! I love undocumented features!!! ;)

Jokes aside, should I look at another implementation? My
goal was to use the collection as the data source in the
UI and then save the data in SQL using OPENXML. I have
heard and read [1] accounts where the performance of the
OPENXML methodology is nearly imcomparable to using the
DataAdapter.Upd ate() method. Any thoughts?

Again, thank you so much for all of your assistance in
this matter; I truely appreciate it.

Harris

[1]: http://www.devx.com/dotnet/Article/16032
-----Original Message-----
Now that you mention it ...

I vaguely remember this coming up before, but I don't

think it was ever
confirmed as a bug ... even though I believe it is ;)

--
HTH
Christoph Schittko [MVP]
Software Architect, .NET Mentor

"Harris Boyce" <hb****@thedotw orks.com> wrote in message
news:4f******* *************** ******@phx.gbl. ..
Christoph,

In a way, please disregard my previous message. I sort
of figured it out...I marked all the properties on my
Category class with XmlAttribute and marked the class
itself with XmlRootAttribut e(ElementName:= "Category") . I then kept the collection class as Serializable; however,
when I created an instance of the XmlSerializer class, I
used the following constructor implementation:

XmlSerializer s = new XmlSerializer(t ypeof(Categorie s),
new XmlRootAttribut e("Category") );

With those changes, I was able to serialize my data in
the format I was looking for...Problem is, I'm not sure
why it worked. I love it when that happens, don't you?

Have any ideas as to why?

Again, I can't thank you enough for all your time.

Harris
>-----Original Message-----
>Harris,
>
>It appears you are selecting the something from the
database which defines
>the default namespace of the element, i.e. it creates an xmlns attribute
>(this may just appear because of line breaks in the
posting)
>None of your classes identify any namespace for the XML
nodes they map to,
>so I suspect your problem is somewhat related to that.
The exception message
>complains also complains about encoutering a namespace
declaration when
>reading the returned XML, so there is something going on here.
>
>However, an empty namespace declaration like your
exception message cites
>should not create a problem, and it didn't when I
verified it.
>
>Have you actually made sure that the Xml you get back
from your stored
>procedure matches the test data you had, complete with
namespace
>declarations , etc? You could, for example, just write
the output from the
>ExecuteXmlRead er to the console or a file by using the
WriteNode method of
>an XmlTextWriter, such as:
>
>XmlTextWrite r writer = new XmlTextWriter( Console.Out ); >writer.WriteSt artElement( "MyRoot" );
>writer.WriteNo de( cmd.ExecuteXmlR eader() );
>writer.WriteEn dElement();
>
>--
>HTH
>Christoph Schittko [MVP]
>Software Architect, .NET Mentor
>
>"Harris Boyce" <hb****@thedotw orks.com> wrote in message >news:26******* *************** ******@phx.gbl. ..
>> Mark,
>>
>> Thanks for your reply. I looked over the article
before
>> I began working on this project and took notes.
However,
>> I'm trying not to couple the application so tightly,
>> thus, I'm looking to avoid using extra dependencies
like
>> the SQLXML Managed Classes. With that said, I attempt to
>> deserialize the XML by calling
>> SqlCommand.Exec uteXmlReader. I've included my stored
>> procedure code below; the second, commented SELECT was a
>> test without creating a root element inside the SP,
>> hoping that the XmlSerializer would see the XML as an
>> Array of Category objects and then I could just load
the
>> array into my strongly-typed collection. I also posted >> the code for the collection, IEnumerator,
and "collection-
>> object" classes in my reply to Christoph if you would
>> like to take a gander at those as well.
>>
>> Thanks for your time and ideas,
>>
>> Harris
>>
>> -- SP CODE: (CREATE PROCEDURE omitted) --
>>
>> SELECT 1 AS tag,
>> null AS parent,
>> NULL AS
>> [Categories!1!!e lement],
>> --'http://tdw/' AS
>> [Categories!1!xm lns],
>> NULL AS [Category!
>> 2!CategoryID],
>> NULL AS [Category!
>> 2!CategoryName],
>> null AS [Category!
>> 2!DisplayOrder]
>>
>> UNION ALL
>>
>> SELECT 2 AS tag,
>> 1 AS parent,
>> NULL AS
>> [Categories!1!!e lement],
>> --NULL AS
>> [Categories!1!xm lns],
>> -1 AS [Category!
>> 2!CategoryID],
>> 'Select Category ' AS
>> [Category!2!Cate goryName],
>> -1 AS [Category!
>> 2!DisplayOrder]
>>
>>
>> UNION ALL
>>
>> SELECT 2 AS tag,
>> 1 AS parent,
>> NULL AS
>> [Categories!1!!e lement],
>> --NULL AS
>> [Categories!1!xm lns],
>> CategoryID AS [Category!
>> 2!CategoryID],
>> [Name] AS [Category!
>> 2!CategoryName],
>> DisplayOrder AS [Category!
>> 2!DisplayOrder]
>> FROM categories
>>
>> ORDER BY [Category!2!Disp layOrder]
>> --
>> -- SELECT 1 AS
>> tag,
>> -- NULL AS parent,
>> -- -- NULL AS
>> [Categories!1!!e lement],
>> -- --NULL AS
>> [Categories!1!xm lns],
>> -- -1 AS [Category!
>> 1!CategoryID],
>> -- 'Select Category ' AS
>> [Category!1!Cate goryName],
>> -- -1 AS [Category!
>> 1!DisplayOrder]
>> --
>> --
>> -- UNION ALL
>> --
>> -- SELECT 1 AS
>> tag,
>> -- NULL AS parent,
>> -- -- NULL AS
>> [Categories!1!!e lement],
>> -- --NULL AS
>> [Categories!1!xm lns],
>> -- CategoryID AS [Category!
>> 1!CategoryID],
>> -- ltrim(rtrim([Name])) AS [Category!
>> 1!CategoryName],
>> -- DisplayOrder AS [Category!
>> 1!DisplayOrder]
>> -- FROM categories
>> --
>> -- ORDER BY [Category!1!Disp layOrder]
>>
>> FOR XML EXPLICIT
>>
>> >-----Original Message-----
>> >Have you read this article?
>> >http://msdn.microsoft.com/library/default.asp?
>> url=/library/en-us/dnexxml/html/xml04212003.asp
>> >
>> >Thanks. Mark
>> >PM - System.Xml
>> >
>> >--
>> >This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> >Use of included script samples are subject to the
terms
>> specified at
>> >http://www.microsoft.com/info/cpyright.htm.
>> >"Harris Boyce" <hb****@thedotw orks.com> wrote in
message
>> >news:06******* *************** ******@phx.gbl. ..
>> >> Hello,
>> >>
>> >> I'm trying to use the FOR XML EXPLICIT clause with
SQL
>> >> Server to deserialize data from my database into a
>> >> strongly-typed collection object that I will use
>> >> throughout my application. I initially tested my
>> design
>> >> by building a collection in code and then
serializing
>> it
>> >> to/from an XML file, which worked fine. However, I >> have
>> >> hit a brick wall trying to restore the data from SQL >> >> Server. I originally had my collection and object
>> >> classes just marked as [Serializable()] without any of
>> >> the XML Serialization attributes; however, replacing >> >> the "standard" serialization attributes with XmlRoot >> and
>> >> XmlAttributes (on properties) has not yielded any
>> >> success. The error I am receiving is as follows
when
>> >> marked as Serializable:
>> >>
>> >> Unhandled Exception:
System.InvalidO perationExcepti on:
>> >> There is an error in XML
>> >> document (1, 2). --->
System.InvalidO perationExcepti on:
>> >> <Categories xmlns=''> wa
>> >> s not expected.
>> >> at
>> >>
>>
Microsoft.Xml.S erialization.Ge neratedAssembly .XmlSerializa >> >> tionReader1.Rea d
>> >> 4_ArrayOfCatego ry()
>> >> --- End of inner exception stack trace ---
>> >> at
>> System.Xml.Seri alization.XmlSe rializer.Deseri alize
>> >> (XmlReader xmlReader, St
>> >> ring encodingStyle)
>> >> at
>> System.Xml.Seri alization.XmlSe rializer.Deseri alize
>> >> (XmlReader xmlReader)
>> >> at PubsData.Tester .main() in F:\Global
>> >> Documents\Visua l Studio .NET Projects\
>> >> PHMCPubs\PubsDa ta\Class1.vb:li ne 64
>> >>
>> >> <Categories> is my root element, followed by an
>> arbitrary
>> >> number of <Category attrib1="xyz" attrib2="123".. ./> >> >> elements.
>> >>
>> >> I appreciate any comments or suggestions that anyone >> may
>> >> have.
>> >>
>> >> Thanks,
>> >>
>> >> Harris
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>
>.
>

.

Nov 11 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1757
by: Kevin Conroy | last post by:
I'm making a 6-tiered application that is using Xml to tie the various layers together in a very clean manner. I'm also trying to do this on the 1.1 Framework although I'm willing to switch to the 1.0 Framework if need be. One layer, which I call a Data Persistence Layer, uses the System.Xml.Serialization.XmlSerializer to transform our...
0
1754
by: Magnus Lindhe | last post by:
Hello, I'm trying to deserialize an XML document into a objects with the XmlSerializer which works fine in my NUnit test suit but fails with a TypeLoadException in the server. The difference between the server and the NUnit test suit is that the server will load the assembly (Flux.Grease.Services.dll) as a plugin in its own AppDomain...
12
8470
by: SJD | last post by:
I've just read Christoph Schittko's article on XmlSerializer: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnxmlnet/html/trblshtxsd.asp . . . and very informative it is too. I, too, am getting nasty FileNotFound exceptions. I've read, and digested the article, and I think I've found a bug -- it's difficult to track,...
3
3163
by: Don McNamara | last post by:
Hi, I've hit quite a strange problem with XmlSerializer on my W2K3 server. When I serialize/deserialize using an exe on my local computer (XP), everything works fine. When I put the code out on the server (W2K3) it throws an exception. It only seems to happen when serializing/deserializing _arrays_ of a type. If I just serialize/deserialize...
4
5401
by: Ultrakorne | last post by:
hi, i have some problems with my client talk to my server... i am using xmlserializer to serialize object and send them to the other side of the connection. I need to send / recive by both client and server. client after login waits all the time listening for objects on a thread, and sends objects on users events on the main thread. server...
8
5438
by: cd~ | last post by:
I can provide a test app, the news server won't allow me to post the files because they are too large (93KB and 1.2KB) I downloaded the ESRI ArcXml schema and generated the classes from the schema using xsd.exe, which took a while because xsd doesn't handle recursive elements very well (StackOverFlow exception during generation). Now that I...
10
4770
by: Henrik Dahl | last post by:
Hello! I have an xml schema which has a date typed attribute. I have used xsd.exe to create a class library for XmlSerializer. The result of XmlSerializer.Serialize(...) should be passed as the value for the parameter of an SqlCommand for inserting the xml document in a column of a table where the column is typed to be of the same xml...
3
3294
by: kimtherkelsen | last post by:
Hi, I want to send XML data from a server to some clients over a network connection using the TCP/IP protocol. If I send the XMLs as byte arrays I need to insert header information in the data to distinguish the XMLs from each other in the stream of data. Is there any way to avoid this (for instance by sending SOAP telegrams))? I have...
0
2792
by: theonlydavewilliams | last post by:
Hi there I'm hoping there's an easy answer to a (hopefully) not too long-winded issue... I'm building a C# web client using a proxy wsdl.exe'd from a wsdl file and six schemas, each in a different namespace. Some schemas extend complex types in others. When i get a soap:Fault from my test server it could contain a serialized exception object...
0
7805
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7726
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8237
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
6454
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5301
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3741
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3752
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1341
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1060
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.