XMLSerializer and SQL Server

[Harris Boyce]

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.InvalidOperationException:
There is an error in XML
document (1, 2). ---> System.InvalidOperationException:
<Categories xmlns=''> wa
s not expected.
at
Microsoft.Xml.Serialization.GeneratedAssembly.XmlS erializa
tionReader1.Read
4_ArrayOfCategory()
--- End of inner exception stack trace ---
at System.Xml.Serialization.XmlSerializer.Deserialize
(XmlReader xmlReader, St
ring encodingStyle)
at System.Xml.Serialization.XmlSerializer.Deserialize
(XmlReader xmlReader)
at PubsData.Tester.main() in F:\Global
Documents\Visual Studio .NET Projects\
PHMCPubs\PubsData\Class1.vb:line 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

[SQL Server Development Team [MSFT]]

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****@thedotworks.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.InvalidOperationException:
There is an error in XML
document (1, 2). ---> System.InvalidOperationException:
<Categories xmlns=''> wa
s not expected.
at
Microsoft.Xml.Serialization.GeneratedAssembly.XmlS erializa
tionReader1.Read
4_ArrayOfCategory()
--- End of inner exception stack trace ---
at System.Xml.Serialization.XmlSerializer.Deserialize
(XmlReader xmlReader, St
ring encodingStyle)
at System.Xml.Serialization.XmlSerializer.Deserialize
(XmlReader xmlReader)
at PubsData.Tester.main() in F:\Global
Documents\Visual Studio .NET Projects\
PHMCPubs\PubsData\Class1.vb:line 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

[Christoph Schittko [MVP]]

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****@thedotworks.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.InvalidOperationException:
There is an error in XML
document (1, 2). ---> System.InvalidOperationException:
<Categories xmlns=''> wa
s not expected.
at
Microsoft.Xml.Serialization.GeneratedAssembly.XmlS erializa
tionReader1.Read
4_ArrayOfCategory()
--- End of inner exception stack trace ---
at System.Xml.Serialization.XmlSerializer.Deserialize
(XmlReader xmlReader, St
ring encodingStyle)
at System.Xml.Serialization.XmlSerializer.Deserialize
(XmlReader xmlReader)
at PubsData.Tester.main() in F:\Global
Documents\Visual Studio .NET Projects\
PHMCPubs\PubsData\Class1.vb:line 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

[Harris Boyce]

Christoph,

Thanks for your reply; below is all of the code -
collection, IEnumerator and CollectionObject 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.ExecuteXMLReader and not
SqlXmlCommand.ExecuteXmlReader 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.Collections
Imports System.Xml.Serialization

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

'<remarks/>
'<System.Xml.Serialization.XmlRootAttribute("Categ ories",
[Namespace]:="", IsNullable:=False)> _
<Serializable()> _
Public Class Categories
Inherits CollectionBase

'<remarks/>
'<System.Xml.Serialization.XmlElementAttribute
("Category",
Form:=System.Xml.Schema.XmlSchemaForm.Unqualified) > _
'Public Items() As Category

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

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

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

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

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

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

End Property

Public Function Add(ByVal aCategory As Category) As
Integer
Return Me.List.Add(aCategory)
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.Contains(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
CategoryEnumerator
Return New CategoryEnumerator(Me)
End Function

End Class

Public Class CategoryEnumerator
Inherits Object
Implements IEnumerator

Private _baseEnumerator As IEnumerator
Private _tempEnumerable As IEnumerable

Public Sub New(ByVal mappings As Categories)

MyBase.new()
Me._tempEnumerable = DirectCast(mappings,
IEnumerable)
Me._baseEnumerator =
Me._tempEnumerable.GetEnumerator

End Sub

Public ReadOnly Property IEnumerator_Current() As
Object Implements System.Collections.IEnumerator.Current
Get
Return _baseEnumerator.Current
End Get
End Property

Public Function IEnumerator_MoveNext() As Boolean
Implements System.Collections.IEnumerator.MoveNext
Return _baseEnumerator.MoveNext()
End Function

Public Sub IEnumerator_Reset() Implements
System.Collections.IEnumerator.Reset
_baseEnumerator.Reset()
End Sub

Public ReadOnly Property Current() As Category
Get
Return DirectCast(_baseEnumerator.Current,
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("Category", [Namespace]:="", IsNullable:=False)

_ <Serializable()> _
Public Class Category

'<remarks/>
Private _CategoryID As Integer
'<System.Xml.Serialization.XmlAttributeAttribute() > _
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.Serialization.XmlAttributeAttribute() > _
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.Serialization.XmlAttributeAttribute() > _
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****@thedotworks.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.InvalidOperationException:
There is an error in XML
document (1, 2). ---> System.InvalidOperationException:
<Categories xmlns=''> wa
s not expected.
at
Microsoft.Xml.Serialization.GeneratedAssembly.XmlS erializa tionReader1.Read
4_ArrayOfCategory()
--- End of inner exception stack trace ---
at System.Xml.Serialization.XmlSerializer.Deserialize (XmlReader xmlReader, St
ring encodingStyle)
at System.Xml.Serialization.XmlSerializer.Deserialize (XmlReader xmlReader)
at PubsData.Tester.main() in F:\Global
Documents\Visual Studio .NET Projects\
PHMCPubs\PubsData\Class1.vb:line 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

.

[Christoph Schittko [MVP]]

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
ExecuteXmlReader to the console or a file by using the WriteNode method of
an XmlTextWriter, such as:

XmlTextWriter writer = new XmlTextWriter( Console.Out );
writer.WriteStartElement( "MyRoot" );
writer.WriteNode( cmd.ExecuteXmlReader() );
writer.WriteEndElement();

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

"Harris Boyce" <hb****@thedotworks.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.ExecuteXmlReader. 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!!element],
--'http://tdw/' AS
[Categories!1!xmlns],
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!!element],
--NULL AS
[Categories!1!xmlns],
-1 AS [Category!
2!CategoryID],
'Select Category ' AS
[Category!2!CategoryName],
-1 AS [Category!
2!DisplayOrder]
UNION ALL

SELECT 2 AS tag,
1 AS parent,
NULL AS
[Categories!1!!element],
--NULL AS
[Categories!1!xmlns],
CategoryID AS [Category!
2!CategoryID],
[Name] AS [Category!
2!CategoryName],
DisplayOrder AS [Category!
2!DisplayOrder]
FROM categories

ORDER BY [Category!2!DisplayOrder]
--
-- SELECT 1 AS
tag,
-- NULL AS parent,
-- -- NULL AS
[Categories!1!!element],
-- --NULL AS
[Categories!1!xmlns],
-- -1 AS [Category!
1!CategoryID],
-- 'Select Category ' AS
[Category!1!CategoryName],
-- -1 AS [Category!
1!DisplayOrder]
--
--
-- UNION ALL
--
-- SELECT 1 AS
tag,
-- NULL AS parent,
-- -- NULL AS
[Categories!1!!element],
-- --NULL AS
[Categories!1!xmlns],
-- CategoryID AS [Category!
1!CategoryID],
-- ltrim(rtrim([Name])) AS [Category!
1!CategoryName],
-- DisplayOrder AS [Category!
1!DisplayOrder]
-- FROM categories
--
-- ORDER BY [Category!1!DisplayOrder]

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****@thedotworks.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.InvalidOperationException:
There is an error in XML
document (1, 2). ---> System.InvalidOperationException:
<Categories xmlns=''> wa
s not expected.
at
Microsoft.Xml.Serialization.GeneratedAssembly.XmlS erializa tionReader1.Read
4_ArrayOfCategory()
--- End of inner exception stack trace ---
at System.Xml.Serialization.XmlSerializer.Deserialize (XmlReader xmlReader, St
ring encodingStyle)
at System.Xml.Serialization.XmlSerializer.Deserialize (XmlReader xmlReader)
at PubsData.Tester.main() in F:\Global
Documents\Visual Studio .NET Projects\
PHMCPubs\PubsData\Class1.vb:line 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

.

[Harris Boyce]

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 =-=- --
<ArrayOfCategory
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Category>
<CategoryID>3</CategoryID>
<CategoryName>Archaeology and Native
Americans</CategoryName>
<DisplayOrder>0</DisplayOrder>
</Category>
<Category>
<CategoryID>4</CategoryID>
<CategoryName>Architecture and Historic
Preservation</CategoryName>
<DisplayOrder>0</DisplayOrder>
</Category>

[Harris Boyce]

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 XmlRootAttribute(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(typeof(Categories),
new XmlRootAttribute("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 theExecuteXmlReader to the console or a file by using the WriteNode method ofan XmlTextWriter, such as:

XmlTextWriter writer = new XmlTextWriter( Console.Out );
writer.WriteStartElement( "MyRoot" );
writer.WriteNode( cmd.ExecuteXmlReader() );
writer.WriteEndElement();

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

"Harris Boyce" <hb****@thedotworks.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.ExecuteXmlReader. 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!!element],
--'http://tdw/' AS
[Categories!1!xmlns],
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!!element],
--NULL AS
[Categories!1!xmlns],
-1 AS [Category!
2!CategoryID],
'Select Category ' AS
[Category!2!CategoryName],
-1 AS [Category!
2!DisplayOrder]
UNION ALL

SELECT 2 AS tag,
1 AS parent,
NULL AS
[Categories!1!!element],
--NULL AS
[Categories!1!xmlns],
CategoryID AS [Category!
2!CategoryID],
[Name] AS [Category!
2!CategoryName],
DisplayOrder AS [Category!
2!DisplayOrder]
FROM categories

ORDER BY [Category!2!DisplayOrder]
--
-- SELECT 1 AS
tag,
-- NULL AS parent,
-- -- NULL AS
[Categories!1!!element],
-- --NULL AS
[Categories!1!xmlns],
-- -1 AS [Category!
1!CategoryID],
-- 'Select Category ' AS
[Category!1!CategoryName],
-- -1 AS [Category!
1!DisplayOrder]
--
--
-- UNION ALL
--
-- SELECT 1 AS
tag,
-- NULL AS parent,
-- -- NULL AS
[Categories!1!!element],
-- --NULL AS
[Categories!1!xmlns],
-- CategoryID AS [Category!
1!CategoryID],
-- ltrim(rtrim([Name])) AS [Category!
1!CategoryName],
-- DisplayOrder AS [Category!
1!DisplayOrder]
-- FROM categories
--
-- ORDER BY [Category!1!DisplayOrder]

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****@thedotworks.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.InvalidOperationException: >> There is an error in XML
>> document (1, 2). ---> System.InvalidOperationException: >> <Categories xmlns=''> wa
>> s not expected.
>> at
>>

Microsoft.Xml.Serialization.GeneratedAssembly.XmlS erializa

>> tionReader1.Read
>> 4_ArrayOfCategory()
>> --- End of inner exception stack trace ---
>> at

System.Xml.Serialization.XmlSerializer.Deserialize

>> (XmlReader xmlReader, St
>> ring encodingStyle)
>> at

System.Xml.Serialization.XmlSerializer.Deserialize

>> (XmlReader xmlReader)
>> at PubsData.Tester.main() in F:\Global
>> Documents\Visual Studio .NET Projects\
>> PHMCPubs\PubsData\Class1.vb:line 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
>>
>>
>
>
>.
>

.

[Christoph Schittko [MVP]]

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****@thedotworks.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 XmlRootAttribute(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(typeof(Categories),
new XmlRootAttribute("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

ExecuteXmlReader to the console or a file by using the

WriteNode method of

an XmlTextWriter, such as:

XmlTextWriter writer = new XmlTextWriter( Console.Out );
writer.WriteStartElement( "MyRoot" );
writer.WriteNode( cmd.ExecuteXmlReader() );
writer.WriteEndElement();

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

"Harris Boyce" <hb****@thedotworks.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.ExecuteXmlReader. 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!!element],
--'http://tdw/' AS
[Categories!1!xmlns],
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!!element],
--NULL AS
[Categories!1!xmlns],
-1 AS [Category!
2!CategoryID],
'Select Category ' AS
[Category!2!CategoryName],
-1 AS [Category!
2!DisplayOrder]
UNION ALL

SELECT 2 AS tag,
1 AS parent,
NULL AS
[Categories!1!!element],
--NULL AS
[Categories!1!xmlns],
CategoryID AS [Category!
2!CategoryID],
[Name] AS [Category!
2!CategoryName],
DisplayOrder AS [Category!
2!DisplayOrder]
FROM categories

ORDER BY [Category!2!DisplayOrder]
--
-- SELECT 1 AS
tag,
-- NULL AS parent,
-- -- NULL AS
[Categories!1!!element],
-- --NULL AS
[Categories!1!xmlns],
-- -1 AS [Category!
1!CategoryID],
-- 'Select Category ' AS
[Category!1!CategoryName],
-- -1 AS [Category!
1!DisplayOrder]
--
--
-- UNION ALL
--
-- SELECT 1 AS
tag,
-- NULL AS parent,
-- -- NULL AS
[Categories!1!!element],
-- --NULL AS
[Categories!1!xmlns],
-- CategoryID AS [Category!
1!CategoryID],
-- ltrim(rtrim([Name])) AS [Category!
1!CategoryName],
-- DisplayOrder AS [Category!
1!DisplayOrder]
-- FROM categories
--
-- ORDER BY [Category!1!DisplayOrder]

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****@thedotworks.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.InvalidOperationException: >> There is an error in XML
>> document (1, 2). ---> System.InvalidOperationException: >> <Categories xmlns=''> wa
>> s not expected.
>> at
>>
Microsoft.Xml.Serialization.GeneratedAssembly.XmlS erializa >> tionReader1.Read
>> 4_ArrayOfCategory()
>> --- End of inner exception stack trace ---
>> at
System.Xml.Serialization.XmlSerializer.Deserialize
>> (XmlReader xmlReader, St
>> ring encodingStyle)
>> at
System.Xml.Serialization.XmlSerializer.Deserialize
>> (XmlReader xmlReader)
>> at PubsData.Tester.main() in F:\Global
>> Documents\Visual Studio .NET Projects\
>> PHMCPubs\PubsData\Class1.vb:line 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
>>
>>
>
>
>.
>

.

[Christoph Schittko [MVP]]

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****@thedotworks.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.Update() 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****@thedotworks.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 XmlRootAttribute(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(typeof(Categories),
new XmlRootAttribute("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
>ExecuteXmlReader to the console or a file by using the
WriteNode method of
>an XmlTextWriter, such as:
>
>XmlTextWriter writer = new XmlTextWriter( Console.Out ); >writer.WriteStartElement( "MyRoot" );
>writer.WriteNode( cmd.ExecuteXmlReader() );
>writer.WriteEndElement();
>
>--
>HTH
>Christoph Schittko [MVP]
>Software Architect, .NET Mentor
>
>"Harris Boyce" <hb****@thedotworks.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.ExecuteXmlReader. 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!!element],
>> --'http://tdw/' AS
>> [Categories!1!xmlns],
>> 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!!element],
>> --NULL AS
>> [Categories!1!xmlns],
>> -1 AS [Category!
>> 2!CategoryID],
>> 'Select Category ' AS
>> [Category!2!CategoryName],
>> -1 AS [Category!
>> 2!DisplayOrder]
>>
>>
>> UNION ALL
>>
>> SELECT 2 AS tag,
>> 1 AS parent,
>> NULL AS
>> [Categories!1!!element],
>> --NULL AS
>> [Categories!1!xmlns],
>> CategoryID AS [Category!
>> 2!CategoryID],
>> [Name] AS [Category!
>> 2!CategoryName],
>> DisplayOrder AS [Category!
>> 2!DisplayOrder]
>> FROM categories
>>
>> ORDER BY [Category!2!DisplayOrder]
>> --
>> -- SELECT 1 AS
>> tag,
>> -- NULL AS parent,
>> -- -- NULL AS
>> [Categories!1!!element],
>> -- --NULL AS
>> [Categories!1!xmlns],
>> -- -1 AS [Category!
>> 1!CategoryID],
>> -- 'Select Category ' AS
>> [Category!1!CategoryName],
>> -- -1 AS [Category!
>> 1!DisplayOrder]
>> --
>> --
>> -- UNION ALL
>> --
>> -- SELECT 1 AS
>> tag,
>> -- NULL AS parent,
>> -- -- NULL AS
>> [Categories!1!!element],
>> -- --NULL AS
>> [Categories!1!xmlns],
>> -- CategoryID AS [Category!
>> 1!CategoryID],
>> -- ltrim(rtrim([Name])) AS [Category!
>> 1!CategoryName],
>> -- DisplayOrder AS [Category!
>> 1!DisplayOrder]
>> -- FROM categories
>> --
>> -- ORDER BY [Category!1!DisplayOrder]
>>
>> 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****@thedotworks.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.InvalidOperationException:
>> >> There is an error in XML
>> >> document (1, 2). --->
System.InvalidOperationException:
>> >> <Categories xmlns=''> wa
>> >> s not expected.
>> >> at
>> >>
>>
Microsoft.Xml.Serialization.GeneratedAssembly.XmlS erializa >> >> tionReader1.Read
>> >> 4_ArrayOfCategory()
>> >> --- End of inner exception stack trace ---
>> >> at
>> System.Xml.Serialization.XmlSerializer.Deserialize
>> >> (XmlReader xmlReader, St
>> >> ring encodingStyle)
>> >> at
>> System.Xml.Serialization.XmlSerializer.Deserialize
>> >> (XmlReader xmlReader)
>> >> at PubsData.Tester.main() in F:\Global
>> >> Documents\Visual Studio .NET Projects\
>> >> PHMCPubs\PubsData\Class1.vb:line 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
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>
>.
>

.