How to Store Tabular Data having () as column name Into XML

• debanjan.ray

  SSC Eights!

  Points: 962

  More actions

  November 9, 2014 at 11:24 pm

  #292368

  Hi Experts,

  We are storing changed data of tables into XML format for auditing purpose. The functionality is already achieved. We are using FOR XML Path clause to convert relational data of tables into XML format.

  Now, a table is having column name with '(' . For example name of the column is, ColumnName(). In this case we can not convert into XML using For XML clause. Showing error as,

  Column name 'columnName()' contains an invalid XML identifier as required by FOR XML; '(' (0x0028) is the first character at fault.

  Please suggest how to fix or over come this issue.

  Thanks in advance,

  Debanjan

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  November 10, 2014 at 12:01 am

  #1757354

  debanjan.ray (11/9/2014)

  ---

  Hi Experts,

  We are storing changed data of tables into XML format for auditing purpose. The functionality is already achieved. We are using FOR XML Path clause to convert relational data of tables into XML format.

  Now, a table is having column name with '(' . For example name of the column is, ColumnName(). In this case we can not convert into XML using For XML clause. Showing error as,

  Column name 'columnName()' contains an invalid XML identifier as required by FOR XML; '(' (0x0028) is the first character at fault.

  Please suggest how to fix or over come this issue.

  Thanks in advance,

  Debanjan

  First a quick question, why using parenthesis in a column name?

  Back to the problem, suggest you use an alias for the column name.

  😎

  USE tempdb;

  GO

  IF OBJECT_ID('dbo.TBL_NAME_TEST') IS NOT NULL DROP TABLE dbo.TBL_NAME_TEST;

  CREATE TABLE dbo.TBL_NAME_TEST

  (

  NT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

  ,NT_COL1 INT NOT NULL

  ,[NT_COL()] INT NOT NULL

  );

  INSERT INTO dbo.TBL_NAME_TEST (NT_COL1, [NT_COL()])

  VALUES (5,10),(10,20),(15,30),(20,40),(25,50);

  SELECT

  X.NT_ID

  ,X.NT_COL1

  ,X.[NT_COL()] AS COL_ALIAS

  FROM dbo.TBL_NAME_TEST X

  FOR XML PATH('Columns')

  ;

  Results

  <Columns>

  <NT_ID>1</NT_ID>

  <NT_COL1>5</NT_COL1>

  <COL_ALIAS>10</COL_ALIAS>

  </Columns>

  <Columns>

  <NT_ID>2</NT_ID>

  <NT_COL1>10</NT_COL1>

  <COL_ALIAS>20</COL_ALIAS>

  </Columns>

  <Columns>

  <NT_ID>3</NT_ID>

  <NT_COL1>15</NT_COL1>

  <COL_ALIAS>30</COL_ALIAS>

  </Columns>

  <Columns>

  <NT_ID>4</NT_ID>

  <NT_COL1>20</NT_COL1>

  <COL_ALIAS>40</COL_ALIAS>

  </Columns>

  <Columns>

  <NT_ID>5</NT_ID>

  <NT_COL1>25</NT_COL1>

  <COL_ALIAS>50</COL_ALIAS>

  </Columns>