There is a cell in the sql table in which data of a varbinary(max) type is stored. This is an array of xml document bytes. How to extract and save from all this a full xml document?

I extract this cell in this way. (I don’t know it’s right)

 SqlDataReader myReader = null; SqlConnection myConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=test;Integrated Security=True"); String queryString = "SELECT TOP 1 planGrafik.[myXML] FROM planGrafik WHERE (regNum='03513000925' OR inn='5410126863') ORDER BY vNum DESC"; myConnection.Open(); SqlCommand command = new SqlCommand(queryString, myConnection); myReader = command.ExecuteReader();
  • @Side Kick is better to add this code to the question - Ruslan_K

2 answers 2

You should already be able to read simple values ​​from the database - in this case it will be easy to get XML from there.

First you need to get an array of bytes:

 var bytes = myReader["имя_поля"] as byte[]; if (bytes == null) { /* что-нибудь сделать */ }

Now you need to build from this set of bytes XML. To do this, you can wrap them in a MemoryStream and read the XML from the stream:

 var doc = new XmlDocument(); doc.Load(new MemoryStream(bytes));

If you prefer to use XLinq, then the MemoryStream also great:

 var doc = XDocument.Load(new MemoryStream(bytes));

Separately, I note that the methods with pre-conversion to the string are incorrect - because you cannot know in advance what XML encoding is written in (the XML encoding is specified within the XML declaration according to the standard). In addition, when using UTF-8 and pre-converting to a string, there may be a problem with the BOM at the beginning of the file.

If for some reason the XML documents are in the database without encoding - you can wrap the MemoryStream in a StreamReader :

 var doc = new XmlDocument(); doc.Load(new StreamReader(new MemoryStream(bytes), Encoding.ВашаКодировка)); var doc = XDocument.Load(new StreamReader(new MemoryStream(bytes), Encoding.ВашаКодировка));

But in such cases, I recommend changing the column type from varbinary to nvarchar ! It will be even easier to work with him:

 var xmlstr = myReader["имя_поля"] as string; if (xmlstr == null) { /* что-нибудь сделать */ } var doc = new XmlDocument(); doc.LoadXml(xmlstr); var doc = XDocument.Parse(xmlstr);
  • Thank you very much! But do not tell me how to save this xml? =) - Side Kick
  • @SideKick Save Where? Back to base, or to file? - Pavel Mayorov
  • @SideKick here are useful links: XDocument , MemoryStream - Pavel Mayorov
  • save to file. I apologize for my stupidity just for me this is all new ... - Side Kick
  • @SideKick usually use Save method - Pavel Mayorov
 SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { byte[] buffer = (byte[])reader[<индекс вашего столбца>];//1 string xml = Encoding.<Кодировка вашего файла>.GetString(buffer) //Encoding.UTF8.GetString(buffer); XmlDocument doc = new XmlDocument(); doc.LoadXml(xml); }

All that is replaced in the triangular brackets with real data (brackets also nafig).

  • This method has problems with encodings. First, it requires guessing the encoding. Secondly, there may be a problem with the initial BOM if the document is in UTF-8. - Pavel Mayorov
  • @Mirdin in buffer should be an array of bytes? (Data from a SQL cell) - Side Kick
  • @SideKick, yes in the buffer array bytes - Mirdin
  • @PavelMayorov, if we have an array of bytes, then we always have to play a guessing game. It would be better, of course, not varbinary, but xml (if there is one), varchar and that would be better. - Mirdin
  • @Mirdin not necessary. By standard, the XML encoding is written in the XML declaration, and if no encoding is specified, UTF-8 is used. - Pavel Mayorov