Is it possible to save the xml document in the sql cell in order to download it to a file if necessary? If you can, how? And preferably with examples. (I just get acquainted with C #).

  • to begin with the question, what kind of cell, what type? - Mirdin
  • read this: Examples of bulk import and export of XML documents ? I also advise you to read: Import data from XML to SQL Server tables - Denis Bubnov
  • As I understand it, you want to store XML documents in a database. In addition to what I dropped to you, you can instead try to make a table with two main columns: an array of bytes read from the file and the file name. And just by standard means we read the file and write to the database. If necessary, read from the database and restore the document. - Denis Bubnov

2 answers 2

First, you need to decide on the database schema used for storage. And for this you need to answer the question "where does XML come from at all"?

If the document can be loaded into the database by the user, then it is best to approach the question of storing it as if you were storing any file — that is, storing it in varbinary(max) , plus a separate column the file name as a string, plus a column for storing file attributes, such as a date create / change.

If XML is formed inside the program, then it is best to save it in nvarchar(max) , so as not to create problems with the encodings for yourself.

How to save data in the database, I hope the author will understand himself. But before you save them there - you must first get them.

Converting an XML document to a string is quite simple:

 XmlDocument doc = ...; var xmlstr = doc.OuterXml; XDocument doc = ...; var xmlstr = doc.ToString();

However, it usually makes no sense to store the entire document as a string as a whole - it’s enough to store its root element:

 XmlDocument doc = ...; var xmlstr = doc.DocumentElement.OuterXml; XDocument doc = ...; var xmlstr = doc.Root.ToString();

The second way to "trim" is usually easily recoverable XML-declaration. This will not affect the inverse transformation, since the root element itself remains a valid document.

If you decide to store the document as an array of bytes, everything becomes slightly more difficult:

 XmlDocument doc = ...; var ms = new MemoryStream(); doc.Save(ms); var bytes = ms.ToArray(); XDocument doc = ...; var ms = new MemoryStream(); doc.Save(ms); var bytes = ms.ToArray();

If there is a need to specify a specific encoding - you should use StreamWriter :

 XmlDocument doc = ...; var ms = new MemoryStream(); using (var writer = new StreamWriter(ms, Encoding.ВашаКодировка)) doc.Save(ms); var bytes = ms.ToArray(); XDocument doc = ...; var ms = new MemoryStream(); using (var writer = new StreamWriter(ms, Encoding.ВашаКодировка)) doc.Save(ms); var bytes = ms.ToArray();

    The easiest way:

    1. Make a column for storing the type nvarchar (MAX).
    2. Convert the xml file to string.

    Code:

     XmlDocument doc = new XmlDocument(); doc.LoadXml("<book genre='novel' ISBN='1-861001-57-5'>" + "<title>Pride And Prejudice</title>" + "</book>"); XmlNode root = doc.DocumentElement; string str = root.OuterXml;
    1. Insert this string into the database (depending on how you work with the data)

    The disadvantage of this method is that such xml will be "dead data" with which you can work only on the client. It is much better to specify the type for the XML column (if your database supports it, for example, MS SQL and MySQL), then you can work with it on the server, but this is a separate very broad topic that also depends on your database.

    Additionally:

    1. XmlNode.OuterXml Property
    2. xml (Transact-SQL)
    • It seems to me not quite correctly stored in the nvarchar(MAX) data type. Unlimited strings often lead to problems. How to deal with encoding? Do not you think so? - Denis Bubnov
    • In principle, this is correct, but there is a nuance: this method works independently of a specific database, the file name and encoding can be stored in separate fields, nvarchar (MAX) can be limited in principle if the maximum file size is known in advance. In addition, in the past, the issue of the TC used in general varbinary (MAX), which is already generally bad. And yes, the type of XML (with the scheme) is certainly better, but it will work only with a specific database. - Mirdin