SQL server changes XML structur when inserted





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty{ margin-bottom:0;
}






up vote
3
down vote

favorite












I'm inserting some XML data to an XML column in SQL server but after the data has been inserted it has been changed by sql server.
Here is the data I insert



              <xsl:value-of select="name/n/given" />
<xsl:text> </xsl:text>
<xsl:value-of select="name/n/family" />


When I read it back, it looks like this



              <xsl:value-of select="name/n/given" />
<xsl:text />
<xsl:value-of select="name/n/family" />


Pay attention to the second line. This is a problem because it changes how the XSLT transformation output will be. The first example will create a space between given and family name, while the second will not create any space, so it will be like JohnJohnsen, while the first one will be like John Johnsen.



Is there some way to solve this?










share|improve this question
























  • middle line correct in both cases, so if it only 1 problem - it is not a problem
    – a_vlad
    6 hours ago










  • It is a problem, because this does change how the XSLT transformation output will be. First line will create a space between given and family name, while the second will not create any space between, so it will be like JohnJohnsen, while the first one will be like John Johnsen
    – Mr Zach
    6 hours ago










  • hmhm, proper space it is " " but not just a space like in this comment (you can not see it)
    – a_vlad
    6 hours ago










  • Perhaps you could use a control character that doesn't exist in the data (like _ or ~) and then replace that with a space at presentation time.
    – Aaron Bertrand
    5 hours ago










  • Yes, I did as a quick fix, thank you.
    – Mr Zach
    5 hours ago

















up vote
3
down vote

favorite












I'm inserting some XML data to an XML column in SQL server but after the data has been inserted it has been changed by sql server.
Here is the data I insert



              <xsl:value-of select="name/n/given" />
<xsl:text> </xsl:text>
<xsl:value-of select="name/n/family" />


When I read it back, it looks like this



              <xsl:value-of select="name/n/given" />
<xsl:text />
<xsl:value-of select="name/n/family" />


Pay attention to the second line. This is a problem because it changes how the XSLT transformation output will be. The first example will create a space between given and family name, while the second will not create any space, so it will be like JohnJohnsen, while the first one will be like John Johnsen.



Is there some way to solve this?










share|improve this question
























  • middle line correct in both cases, so if it only 1 problem - it is not a problem
    – a_vlad
    6 hours ago










  • It is a problem, because this does change how the XSLT transformation output will be. First line will create a space between given and family name, while the second will not create any space between, so it will be like JohnJohnsen, while the first one will be like John Johnsen
    – Mr Zach
    6 hours ago










  • hmhm, proper space it is " " but not just a space like in this comment (you can not see it)
    – a_vlad
    6 hours ago










  • Perhaps you could use a control character that doesn't exist in the data (like _ or ~) and then replace that with a space at presentation time.
    – Aaron Bertrand
    5 hours ago










  • Yes, I did as a quick fix, thank you.
    – Mr Zach
    5 hours ago













up vote
3
down vote

favorite









up vote
3
down vote

favorite











I'm inserting some XML data to an XML column in SQL server but after the data has been inserted it has been changed by sql server.
Here is the data I insert



              <xsl:value-of select="name/n/given" />
<xsl:text> </xsl:text>
<xsl:value-of select="name/n/family" />


When I read it back, it looks like this



              <xsl:value-of select="name/n/given" />
<xsl:text />
<xsl:value-of select="name/n/family" />


Pay attention to the second line. This is a problem because it changes how the XSLT transformation output will be. The first example will create a space between given and family name, while the second will not create any space, so it will be like JohnJohnsen, while the first one will be like John Johnsen.



Is there some way to solve this?










share|improve this question















I'm inserting some XML data to an XML column in SQL server but after the data has been inserted it has been changed by sql server.
Here is the data I insert



              <xsl:value-of select="name/n/given" />
<xsl:text> </xsl:text>
<xsl:value-of select="name/n/family" />


When I read it back, it looks like this



              <xsl:value-of select="name/n/given" />
<xsl:text />
<xsl:value-of select="name/n/family" />


Pay attention to the second line. This is a problem because it changes how the XSLT transformation output will be. The first example will create a space between given and family name, while the second will not create any space, so it will be like JohnJohnsen, while the first one will be like John Johnsen.



Is there some way to solve this?







sql-server xml






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited 4 hours ago









Michael Green

13.8k82957




13.8k82957










asked 6 hours ago









Mr Zach

1452




1452












  • middle line correct in both cases, so if it only 1 problem - it is not a problem
    – a_vlad
    6 hours ago










  • It is a problem, because this does change how the XSLT transformation output will be. First line will create a space between given and family name, while the second will not create any space between, so it will be like JohnJohnsen, while the first one will be like John Johnsen
    – Mr Zach
    6 hours ago










  • hmhm, proper space it is " " but not just a space like in this comment (you can not see it)
    – a_vlad
    6 hours ago










  • Perhaps you could use a control character that doesn't exist in the data (like _ or ~) and then replace that with a space at presentation time.
    – Aaron Bertrand
    5 hours ago










  • Yes, I did as a quick fix, thank you.
    – Mr Zach
    5 hours ago


















  • middle line correct in both cases, so if it only 1 problem - it is not a problem
    – a_vlad
    6 hours ago










  • It is a problem, because this does change how the XSLT transformation output will be. First line will create a space between given and family name, while the second will not create any space between, so it will be like JohnJohnsen, while the first one will be like John Johnsen
    – Mr Zach
    6 hours ago










  • hmhm, proper space it is " " but not just a space like in this comment (you can not see it)
    – a_vlad
    6 hours ago










  • Perhaps you could use a control character that doesn't exist in the data (like _ or ~) and then replace that with a space at presentation time.
    – Aaron Bertrand
    5 hours ago










  • Yes, I did as a quick fix, thank you.
    – Mr Zach
    5 hours ago
















middle line correct in both cases, so if it only 1 problem - it is not a problem
– a_vlad
6 hours ago




middle line correct in both cases, so if it only 1 problem - it is not a problem
– a_vlad
6 hours ago












It is a problem, because this does change how the XSLT transformation output will be. First line will create a space between given and family name, while the second will not create any space between, so it will be like JohnJohnsen, while the first one will be like John Johnsen
– Mr Zach
6 hours ago




It is a problem, because this does change how the XSLT transformation output will be. First line will create a space between given and family name, while the second will not create any space between, so it will be like JohnJohnsen, while the first one will be like John Johnsen
– Mr Zach
6 hours ago












hmhm, proper space it is " " but not just a space like in this comment (you can not see it)
– a_vlad
6 hours ago




hmhm, proper space it is " " but not just a space like in this comment (you can not see it)
– a_vlad
6 hours ago












Perhaps you could use a control character that doesn't exist in the data (like _ or ~) and then replace that with a space at presentation time.
– Aaron Bertrand
5 hours ago




Perhaps you could use a control character that doesn't exist in the data (like _ or ~) and then replace that with a space at presentation time.
– Aaron Bertrand
5 hours ago












Yes, I did as a quick fix, thank you.
– Mr Zach
5 hours ago




Yes, I did as a quick fix, thank you.
– Mr Zach
5 hours ago










2 Answers
2






active

oldest

votes

















up vote
3
down vote













This page of the SQL Server documentation says




The data is stored in an internal representation that ... may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.




For your example I suppose it considers the middle tag's white space to be not significant and is therefore free to refactor the representation. I don't think there is a fix for this; it is just how SQL Server implements the XML data type.



Work-arounds would include using a place-holder instead of white space as @Aaron says. The consumer must remember to insert and strip out these tokens. Alternatively define the column as nvarchar instead of XML. This will definitely preserve all white space and any other formatting. A quick example:



create table x(i nvarchar(99), j xml);
insert x values ('<a> </a>', '<a> </a>'); -- note the space
select * from x

i j
---------- -------
<a> </a> <a />


The nvarchar column preserves the input format, the XML column does not.



You wil lose the ability to use XPATH in SQL queries. If the XML is only shredded in the application this is immaterial. Further the character string could be compressed saving space in the DB, if this is significant for you.






share|improve this answer





















  • You could probably still use XPATH in queries against the XML version, even if you just let it reformat, as long as you aren't relying on a hit (or miss) for the insignificant space there.
    – Aaron Bertrand
    4 hours ago


















up vote
1
down vote













You can use xml:space = "preserve" on the nodes where you want to keep the space. Using xml:space is "only a signal of intent" but SQL server is kind to us here.



For one node



declare @X xml =
'<root>
<element xml:space = "preserve"> </element>
<element> </element>
</root>'

select @X;


Result:



<root>
<element xml:space="preserve"> </element>
<element />
</root>


Entire document:



declare @X xml =
'<root xml:space = "preserve">
<element> </element>
<element> </element>
</root>'

select @X;


Result:



<root xml:space="preserve">
<element> </element>
<element> </element>
</root>





share|improve this answer





















    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














     

    draft saved


    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f223496%2fsql-server-changes-xml-structur-when-inserted%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    3
    down vote













    This page of the SQL Server documentation says




    The data is stored in an internal representation that ... may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.




    For your example I suppose it considers the middle tag's white space to be not significant and is therefore free to refactor the representation. I don't think there is a fix for this; it is just how SQL Server implements the XML data type.



    Work-arounds would include using a place-holder instead of white space as @Aaron says. The consumer must remember to insert and strip out these tokens. Alternatively define the column as nvarchar instead of XML. This will definitely preserve all white space and any other formatting. A quick example:



    create table x(i nvarchar(99), j xml);
    insert x values ('<a> </a>', '<a> </a>'); -- note the space
    select * from x

    i j
    ---------- -------
    <a> </a> <a />


    The nvarchar column preserves the input format, the XML column does not.



    You wil lose the ability to use XPATH in SQL queries. If the XML is only shredded in the application this is immaterial. Further the character string could be compressed saving space in the DB, if this is significant for you.






    share|improve this answer





















    • You could probably still use XPATH in queries against the XML version, even if you just let it reformat, as long as you aren't relying on a hit (or miss) for the insignificant space there.
      – Aaron Bertrand
      4 hours ago















    up vote
    3
    down vote













    This page of the SQL Server documentation says




    The data is stored in an internal representation that ... may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.




    For your example I suppose it considers the middle tag's white space to be not significant and is therefore free to refactor the representation. I don't think there is a fix for this; it is just how SQL Server implements the XML data type.



    Work-arounds would include using a place-holder instead of white space as @Aaron says. The consumer must remember to insert and strip out these tokens. Alternatively define the column as nvarchar instead of XML. This will definitely preserve all white space and any other formatting. A quick example:



    create table x(i nvarchar(99), j xml);
    insert x values ('<a> </a>', '<a> </a>'); -- note the space
    select * from x

    i j
    ---------- -------
    <a> </a> <a />


    The nvarchar column preserves the input format, the XML column does not.



    You wil lose the ability to use XPATH in SQL queries. If the XML is only shredded in the application this is immaterial. Further the character string could be compressed saving space in the DB, if this is significant for you.






    share|improve this answer





















    • You could probably still use XPATH in queries against the XML version, even if you just let it reformat, as long as you aren't relying on a hit (or miss) for the insignificant space there.
      – Aaron Bertrand
      4 hours ago













    up vote
    3
    down vote










    up vote
    3
    down vote









    This page of the SQL Server documentation says




    The data is stored in an internal representation that ... may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.




    For your example I suppose it considers the middle tag's white space to be not significant and is therefore free to refactor the representation. I don't think there is a fix for this; it is just how SQL Server implements the XML data type.



    Work-arounds would include using a place-holder instead of white space as @Aaron says. The consumer must remember to insert and strip out these tokens. Alternatively define the column as nvarchar instead of XML. This will definitely preserve all white space and any other formatting. A quick example:



    create table x(i nvarchar(99), j xml);
    insert x values ('<a> </a>', '<a> </a>'); -- note the space
    select * from x

    i j
    ---------- -------
    <a> </a> <a />


    The nvarchar column preserves the input format, the XML column does not.



    You wil lose the ability to use XPATH in SQL queries. If the XML is only shredded in the application this is immaterial. Further the character string could be compressed saving space in the DB, if this is significant for you.






    share|improve this answer












    This page of the SQL Server documentation says




    The data is stored in an internal representation that ... may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.




    For your example I suppose it considers the middle tag's white space to be not significant and is therefore free to refactor the representation. I don't think there is a fix for this; it is just how SQL Server implements the XML data type.



    Work-arounds would include using a place-holder instead of white space as @Aaron says. The consumer must remember to insert and strip out these tokens. Alternatively define the column as nvarchar instead of XML. This will definitely preserve all white space and any other formatting. A quick example:



    create table x(i nvarchar(99), j xml);
    insert x values ('<a> </a>', '<a> </a>'); -- note the space
    select * from x

    i j
    ---------- -------
    <a> </a> <a />


    The nvarchar column preserves the input format, the XML column does not.



    You wil lose the ability to use XPATH in SQL queries. If the XML is only shredded in the application this is immaterial. Further the character string could be compressed saving space in the DB, if this is significant for you.







    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered 4 hours ago









    Michael Green

    13.8k82957




    13.8k82957












    • You could probably still use XPATH in queries against the XML version, even if you just let it reformat, as long as you aren't relying on a hit (or miss) for the insignificant space there.
      – Aaron Bertrand
      4 hours ago


















    • You could probably still use XPATH in queries against the XML version, even if you just let it reformat, as long as you aren't relying on a hit (or miss) for the insignificant space there.
      – Aaron Bertrand
      4 hours ago
















    You could probably still use XPATH in queries against the XML version, even if you just let it reformat, as long as you aren't relying on a hit (or miss) for the insignificant space there.
    – Aaron Bertrand
    4 hours ago




    You could probably still use XPATH in queries against the XML version, even if you just let it reformat, as long as you aren't relying on a hit (or miss) for the insignificant space there.
    – Aaron Bertrand
    4 hours ago












    up vote
    1
    down vote













    You can use xml:space = "preserve" on the nodes where you want to keep the space. Using xml:space is "only a signal of intent" but SQL server is kind to us here.



    For one node



    declare @X xml =
    '<root>
    <element xml:space = "preserve"> </element>
    <element> </element>
    </root>'

    select @X;


    Result:



    <root>
    <element xml:space="preserve"> </element>
    <element />
    </root>


    Entire document:



    declare @X xml =
    '<root xml:space = "preserve">
    <element> </element>
    <element> </element>
    </root>'

    select @X;


    Result:



    <root xml:space="preserve">
    <element> </element>
    <element> </element>
    </root>





    share|improve this answer

























      up vote
      1
      down vote













      You can use xml:space = "preserve" on the nodes where you want to keep the space. Using xml:space is "only a signal of intent" but SQL server is kind to us here.



      For one node



      declare @X xml =
      '<root>
      <element xml:space = "preserve"> </element>
      <element> </element>
      </root>'

      select @X;


      Result:



      <root>
      <element xml:space="preserve"> </element>
      <element />
      </root>


      Entire document:



      declare @X xml =
      '<root xml:space = "preserve">
      <element> </element>
      <element> </element>
      </root>'

      select @X;


      Result:



      <root xml:space="preserve">
      <element> </element>
      <element> </element>
      </root>





      share|improve this answer























        up vote
        1
        down vote










        up vote
        1
        down vote









        You can use xml:space = "preserve" on the nodes where you want to keep the space. Using xml:space is "only a signal of intent" but SQL server is kind to us here.



        For one node



        declare @X xml =
        '<root>
        <element xml:space = "preserve"> </element>
        <element> </element>
        </root>'

        select @X;


        Result:



        <root>
        <element xml:space="preserve"> </element>
        <element />
        </root>


        Entire document:



        declare @X xml =
        '<root xml:space = "preserve">
        <element> </element>
        <element> </element>
        </root>'

        select @X;


        Result:



        <root xml:space="preserve">
        <element> </element>
        <element> </element>
        </root>





        share|improve this answer












        You can use xml:space = "preserve" on the nodes where you want to keep the space. Using xml:space is "only a signal of intent" but SQL server is kind to us here.



        For one node



        declare @X xml =
        '<root>
        <element xml:space = "preserve"> </element>
        <element> </element>
        </root>'

        select @X;


        Result:



        <root>
        <element xml:space="preserve"> </element>
        <element />
        </root>


        Entire document:



        declare @X xml =
        '<root xml:space = "preserve">
        <element> </element>
        <element> </element>
        </root>'

        select @X;


        Result:



        <root xml:space="preserve">
        <element> </element>
        <element> </element>
        </root>






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered 37 mins ago









        Mikael Eriksson

        17.3k34683




        17.3k34683






























             

            draft saved


            draft discarded



















































             


            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f223496%2fsql-server-changes-xml-structur-when-inserted%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            flock() on closed filehandle LOCK_FILE at /usr/bin/apt-mirror

            Mangá

            Eduardo VII do Reino Unido