locked
Problem Invalid Characters in Result of FOR XML RRS feed

  • Question

  • Hi;

    I have a Select Query while Return a XML Document,but DataTypes Columns (Report) table is Nvarchar and have Whitespace in name Columns.

    While me Execute Query WhiteSpace in ColumnNames convert to (_0020_).

    Select Query: (SELECT * FROM Reports FOR XML RAW('Report'),ROOT('Reports'))

    How can me encode Invalid Column Name?

    Thanks,mohsen




    Tuesday, September 1, 2009 2:49 PM

Answers

  • It is not clear to me what you want to achieve or what the problem is. This forum is about XML in the .NET framework, if you want to decode the name 'col_x0020_1' with .NET code then you can use the XmlConvert class in the namespace System.Xml:

    XmlConvert.DecodeName("col_x0020_1")

    That will yield "col 1".
    MVP XML My blog
    Tuesday, September 1, 2009 4:50 PM

All replies

  • Can you give an example of the data you have and an example of the result of the FOR XML query? Then please explain why you think you need to escape something.
    When I try a query with SQL Server Express 2005 then an column name 'col 1' in the database table is escaped as 'col_x0020_1' in the XML which is a well-formed name.
    So I am not sure what your problem is.
    MVP XML My blog
    Tuesday, September 1, 2009 4:02 PM
  • Can you give an example of the data you have and an example of the result of the FOR XML query? Then please explain why you think you need to escape something.
    When I try a query with SQL Server Express 2005 then an column name 'col 1' in the database table is escaped as 'col_x0020_1' in the XML which is a well-formed name.
    So I am not sure what your problem is.
    MVP XML My blog

      How me convert 'col_x0020_1' to 'col 1'?

    Thanks,mohsen
    Tuesday, September 1, 2009 4:28 PM
  • FOR XML maps SQL column names to XML element or attribute names where spaces are not allowed. So you can't have 'col 1' as an element or attribute name in XML.
    MVP XML My blog
    Tuesday, September 1, 2009 4:30 PM
  • FOR XML maps SQL column names to XML element or attribute names where spaces are not allowed. So you can't have 'col 1' as an element or attribute name in XML.
    MVP XML My blog

     How me convert 'col_x0020_1'  to 'col 1' in HTML?

    Thanks,mohsen
    Tuesday, September 1, 2009 4:33 PM
  • It is not clear to me what you want to achieve or what the problem is. This forum is about XML in the .NET framework, if you want to decode the name 'col_x0020_1' with .NET code then you can use the XmlConvert class in the namespace System.Xml:

    XmlConvert.DecodeName("col_x0020_1")

    That will yield "col 1".
    MVP XML My blog
    Tuesday, September 1, 2009 4:50 PM