0

I am rewriting an old application that used to upload XML files using an old .aspx form. We're getting rid of the form and want to automate the process. As I'm doing this I'm seeing differences in the XML formatting and want to make sure that I process the XML the same way that the old app did since another process relies on the format.

The old format in VB, used a MemoryStream, read all of the bytes and returned the Stream and created a big inline SQL insert statement to load the data into the DB.

The new format uses C# and XDocument. The line

XDocument.Load(fileName)

Returns XML in the correct format, but I don't see an XML header and the data is surrounded by curly braces -> "{ }" In the XML Viewer in Visual Studio, the data looks fine, though, so perhaps this is all a residue of Visual Studio?

In any case, I need to get the XML to include the header when inserting into the database. Any advice would be appreciated! Thank you!

ewomack
  • 607
  • 9
  • 23
  • Have you tried specifying the namespace when querying the XmlDoc? – daveBM May 09 '17 at 14:48
  • The Load method still keeps the XML identification line, it is just the view that doesn't show the identification. – jdweng May 09 '17 at 15:10
  • `XDocument` has `XDeclaration` property. You can read the header `XDocument doc = XDocument.Load("MyFile.xml");` `var header = doc.Declaration.ToString();`. Hope ths helps. – FortyTwo May 09 '17 at 15:21
  • So I need to translate the XDocument format into a database friendly format (likely a string) - the .ToString() seems to obliterate the header, but maybe Declaration will restore that? Putting .ToString() on the XDocument seems to really change the format - it removes the header and adds "\r\n" into the string. – ewomack May 09 '17 at 15:42
  • Thank you for your response @Shnugo. Sorry for the delay. I won't go into why it took so long except to say: 2 new computers, vacation and too many projects. Thank you again! – ewomack Jun 13 '17 at 01:24

1 Answers1

0

You should not think about XML as text with some fancy extras... How is this stored in SQL Server? If the target column is a real XML type you should not bother about the visual format at all. If the visual format is of any importance, than it is the problem of the consuming / reading software...

If you store the XML in a string typed column, you can store literally everything, even invalid XML. If you want to use this XML in SQL Server with XML methods like .value() or .nodes() you will need the real XML type... If you can control this, make sure the target is a real XML typed variable or column!

The xml declaration (the processing instruction stating the encoding and the xml-version in most cases) will be ommitted in any case. SQL Server does not accept such a declaration with its native XML type.

You get into troubles, if you store the XML in a string typed column together with an xml declaration. In this case you should use encoding="utf-16" and you must store this in a NVARCHAR(MAX) column.

If the actual encoding is NCHAR or NVARCHAR SQL Server expects a unicode encoded string. If the column is without the N, SQL Server expects extended ASCII (collation dependant). You cannot mix this! You cannot convert a string with an xml-declaration stating utf-16 if it is a VARCHAR (and vice-versa).

Anyway one should avoid ASCII encoded XML. This will get you into troubles with non-latin characters and will need expensive operations due to the fact, that SQL Server stores XML in a unicode based tree structure internally.

About namespaces, if there are any, you must be very carefull. They must be part of the XML, otherwise you won't be able to read the XML later.

In this answer you find the code to convert an XDocument into XmlDocument. Then use the property OuterXml to get the textual representation of the XML. As C# internally uses unicode strings, just pass this over into a variable or column of type XML or NVARCHAR(MAX).

Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114