January 15, 2020 at 10:05 am
Hello community!
In a project I have to use the XML column datatype. It's the first time I am using this datatype and I have some questions concerning this.
Background: The project is programmed in C# and is connected via NHibernate to the SQL Server. I can save and query all data. However, I am missing the
<?xml version="1.0" encoding="utf-16"?>
node in the stored data. I checked my code and the element is in the XML documents that I want to store. As soon as the insert/update is executed I can see the following data in the profiler
declare @p6 xml
set @p6=convert(xml,N'<RsSn><Layermapping xmlns="someNamespace">
<Layer layerName="0" layerDescription="5876548"/>
<Layer layerName="1" layerDescription="5876549"/>
<Layer layerName="2" layerDescription="5876550"/>
<Layer layerName="3" layerDescription="5876551"/>
<Layer layerName="4" layerDescription="Tackern1"/>
<Layer layerName="5" layerDescription="5876600"/>
<Layer layerName="6" layerDescription="5876601"/>
<Layer layerName="7" layerDescription="5876602"/>
<Layer layerName="8" layerDescription="5876603"/>
</Layermapping>
</RsSn>')
So the element has been remove by NHibernate. I checked some other examples on the web concerning the XML datatype on SQL server. So far, I have never seen an example where the
<?xml version="1.0" encoding="utf-8"?>
was shown in the screenshots of the Management Studio.
a) Is the node with the encoding and version information generally removed when stored in the SQL server?
b) Is this only behaviour of NHibernate?
c) While checking the table design, I have seen the entry "Is XML Document" in the column properties. The property is enabled if the (Schema Collection) is checked. What is happening if "Is XML Document" is set to true?
Thank you
January 15, 2020 at 12:41 pm
A & B) Sounds like someone isn't making well formed XML. This is a coding issue within nHibernate and no, it's not native, immutable, behavior. They're configured wrong and/or coding wrong. They can fix it.
C) I strongly recommend you go and read the XML documentation at Microsoft. Enabling the Is XML Document means that SQL Server validates that you're working with a well formed XML document. See A&B above.
Fair warning: Yes, SQL Server can store XML. No, it's not terribly good at it. Yes, it uses lots and resources, memory and CPU, in the validation and querying of XML. Yes, it's absolutely worth exploring whether or not you should be looking at almost any other solution, not least of which is using the relational database engine to store, oh, I don't know, relational data. Yes, XQuery is a giant pain to learn and maintain (do what all the rest of us do, find one working query, copy it, modify as needed).
Best of luck.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 15, 2020 at 2:11 pm
Hello Mr. Fritchey
Thank you for the confirmation and summary for my questions.
The XML documents - better say snippets - are validated in the software, not on the SQL server as there are multiple schemas. The column itself is included in statements but only as part of the result.
Best Regards
Alex
January 15, 2020 at 2:28 pm
SQL Server does not store or display the version/encoding element as it is not part of the XML structure. It will store the XML data either as ASCII/ISOxxxx or UTF16 depending on the data inserted, UTF-8 is not supported for the data type and is converted on insert.
😎
Quick question, if you are using the XML within SQL Server, why do you need the version/encoding header element?
January 15, 2020 at 2:54 pm
> Quick question, if you are using the XML within SQL Server, why do you need the version/encoding header element?
Via the frontend, the data is loaded into an editor. Once the user executes the save command, I have to convert the data from the editor into an XML document. As the version information is now missing, I cannot directly do that.
I have to create an XmlDocument object, add the version information, then append the XML from the editor to the XmlDocument and save the data back to the database. Without the version information, the XmlDocument class throws an error when the data from the editor is added.
January 15, 2020 at 3:10 pm
> Quick question, if you are using the XML within SQL Server, why do you need the version/encoding header element?
Via the frontend, the data is loaded into an editor. Once the user executes the save command, I have to convert the data from the editor into an XML document. As the version information is now missing, I cannot directly do that.
I have to create an XmlDocument object, add the version information, then append the XML from the editor to the XmlDocument and save the data back to the database. Without the version information, the XmlDocument class throws an error when the data from the editor is added.
If you are only storing the XML and not querying it or manipulating it in SQL Server, then I suggest you should use VARBINARY(MAX) rather than the XML data type.
😎
You will still get the correct BOM from the XML data type but since the UTF-8 is converted, relying on the BOM is kind of a dirty workaround, which I do not reccomend.
January 15, 2020 at 3:12 pm
If you are only storing the XML and not querying it or manipulating it in SQL Server, then I suggest you should use VARBINARY(MAX) rather than the XML data type.
😎
That was my first proposal. Customer wants to see the XML data well formed via SSMS.
January 15, 2020 at 3:15 pm
Eirikur Eiriksson wrote:If you are only storing the XML and not querying it or manipulating it in SQL Server, then I suggest you should use VARBINARY(MAX) rather than the XML data type.
😎
That was my first proposal. Customer wants to see the XML data well formed via SSMS.
Just create a view with the XML conversion for the customer.
😎
January 15, 2020 at 4:06 pm
Alex-489474 wrote:Eirikur Eiriksson wrote:If you are only storing the XML and not querying it or manipulating it in SQL Server, then I suggest you should use VARBINARY(MAX) rather than the XML data type.
😎
That was my first proposal. Customer wants to see the XML data well formed via SSMS.
Just create a view with the XML conversion for the customer.
😎
Exactly
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply