locked
row name ' ' contains an invalid xml identifier as required by for xml in a query RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I was trying to execute this query,but it is showing the error as below

    row name '  ' contains an invalid xml identifier as required by for xml in a query

    Please help me out  anyone if you can?

    select          ex1.[comet_organization],

                     ex1.[Company],

     ex1.[Address1],
                    ex1.[street_number],
                  ex1.[first_name],
              acconumber = REPLACE ( ( SELECT acconumber AS [text()]
              FROM sl_cdce_cim_daily_customer_extract EX2
              WHERE EX2.[first_name] = EX1.[first_name]
              order by acconumber
              FOR XML PATH(' ')
              ),' ',',')
            FROM sl_cdce_cim_daily_customer_extract ex1
            group by ex1.[first_name],
                    ex1.[comet_organization],
                    ex1.[Company],
                    ex1.[Address1],
                    ex1.[street_number]


                   

    Friday, October 9, 2015 10:44 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi lava8,

    According to your description, you get the error “row name '  ' contains an invalid xml identifier as required by for xml in a query”  when executing the SQL query. However, when you use text() and path() to specify column names in the query, parameter in the PATH ('') should be null rather than the space character. The right SQL query is shown as below:

    select  ex1.[comet_organization], ex1.[Company], ex1.[Address1], ex1.[street_number],
ex1.[first_name],  acconumber = REPLACE ( ( SELECT acconumber AS [text()]
           FROM sl_cdce_cim_daily_customer_extract EX2
           WHERE EX2.[first_name] = EX1.[first_name]
           order by acconumber
          FOR XML PATH('')), '', ',')
         FROM sl_cdce_cim_daily_customer_extract ex1
         group by ex1.[first_name],
                 ex1.[comet_organization],
                 ex1.[Company],
                 ex1.[Address1],
                 ex1.[street_number]

    For more information of retrieving a SQL query result as XML, please refer to the following article.
    column names with the path are specified as data()

    Regards,
    Ice Fan 

    Ice Fan
    TechNet Community Support



    Monday, October 12, 2015 6:16 AM