Mary wrote:
Can you show us your FOR XML?
I guess it creates elements in no namespace while you seem to have
decided to use the XmlWriter to write out a root element in the
namespace http://www.wisconsinedi.org to then simply copy the nodes from
the XmlReader. To ensure the nodes are copied correctly the XmlWriter
has to add the xmlns="" as only that way the nodes remain in no
namespace. You seem to expect that XmlWriter simply spews out some text
concatenation but it is not doing that, if you want text concatenation
then don't use an XML API, use a StringBuilder or StringWriter. That is
however not recommended at all for building XML.

To fix the problem you could consider to create the root element on the
server too, since MS SQL Server 2005 you can use
FOR XML, ROOT("root-element-name")
to have MS SQL server create well-formed document with a root element
and not only a fragment.
As you want to create elements in a namespace you will need to use WITH
XMLNAMESPACES (DEFAULT 'http://www.wisconsinedi.org')

Other ways would be to not use WriteNode but write your own code that
pulls in nodes from the XmlReader and writes out nodes to the XmlWriter
as needed in the correct namespace.

Are you loading your XML into a System.Xml.XmlDocument?
In that case I would certainly use FOR XML ROOT to have SQL server
output a well-formed document with the root element you want, then
simply use Load(xr) on the XmlDocument object and then add any
xsi:schemaLocation or other attributes with the DOM APIs (e.g.
SetAttribute()) if you really need them.

--

Martin Honnen --- MVP Data Platform Development
http://msmvps.com/blogs/martin_honnen/

Mary wrote:

I am sure I suggested to use the 'Load' method, not the 'LoadXML'
method. Also note that that suggestion is only possible if you use the
FOR XML ROOT instruction to ensure SQL server outputs a root element.

--

Martin Honnen --- MVP Data Platform Development
http://msmvps.com/blogs/martin_honnen/

Thank you for leading me in the right direction. I now have the root and
detail elements coming from the SQL stored procedure and am setting the root
attributes as you suggested .... it is working perfectly! The last issue I
need to take care of is inserting an element <header_record> between the
root <submission> and <detail_record>. The header record only appears once
before all the detail is generated. Should I create the header record in SQL
or in .net?

Thanks again!


"Martin Honnen" <mahotrash (AT) yahoo (DOT) de> wrote