Questions concerning XML datatype

  • Alex-489474

    Ten Centuries

    Points: 1381

    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

     

  • Grant Fritchey

    SSC Guru

    Points: 395846

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Alex-489474

    Ten Centuries

    Points: 1381

    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

  • Eirikur Eiriksson

    SSC Guru

    Points: 182399

    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?

  • Alex-489474

    Ten Centuries

    Points: 1381

    >Β 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.

     

  • Eirikur Eiriksson

    SSC Guru

    Points: 182399

    Alex-489474 wrote:

    >Β 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.

  • Alex-489474

    Ten Centuries

    Points: 1381

    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.

     

  • Eirikur Eiriksson

    SSC Guru

    Points: 182399

    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.

    😎

     

  • Grant Fritchey

    SSC Guru

    Points: 395846

    Eirikur Eiriksson wrote:

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply