1

It looks like my query is converting char(13) wrong? If it isn't wrong can someone please explain this?

Query:

;with CTE1 as 
    (
        select 
            Product
            , (select t.* for xml raw('row'), type) as Data
        from 
            [PIM].[dbo].[MasterFeatures] as t
    )
, CTE2 as 
    (
        select
             Product,
             F.C.value('local-name(.)', 'nvarchar(128)') as Field,
             F.C.value('.', 'nvarchar(max)') as Data
             , GETDATE() AS createdDate
        from 
            CTE1 as c
            outer apply c.Data.nodes('row/@*') as F(C)
    )
select * 
from CTE2 x
WHERE x.Field<>'Product'

result with issue pointed out:

enter image description here

I thought CHAR(13) was supposed to be:

  '&#x0D;'
Dale K
  • 25,246
  • 15
  • 42
  • 71
Doug Coats
  • 6,255
  • 9
  • 27
  • 49

1 Answers1

2

XML allows flexibility in how numeric character references are presented. The following representations are completely equivalent:

&#xD; &#x0D; &#x000D; &#x00000000D; &#xd; &#x0d; &#13; &#00000000013;

If you care which of these is generated, then that means that the consumer of your data is not a conformant XML parser. Sadly it's quite common for people to try to parse XML "by hand" without even reading the XML specification first, so this is a not uncommon problem.

This has nothing to do with XPath by the way. The XPath processor doesn't serialize its result; some other bit of software is doing that.

Michael Kay
  • 156,231
  • 11
  • 92
  • 164
  • Awesome, this is great information. Now that I know there is flexibility in the correct outputs I can spend less time researching things. I should also read the XML specification - this issue arose due to my experimenting with SQL Server's xml options, and if I am going to use them, I guess I should learn the basics. – Doug Coats Oct 29 '19 at 13:32