CODEWITHSUNDEEP
sqlsql-serversql-server-2008dynamic-sqlsqlxml
John Keats
John Keats

Reputation: 187

How to create a snapshot of a table, it's columns, and corresponding values, stored in an xml object

DECLARE @TableName NVARCHAR(MAX) = 'my_TableNameName';
DECLARE @SQL NVARCHAR(500);
DECLARE @PrimaryID NVARCHAR(MAX) = '228';
SELECT @SQL = 
      N'SELECT 
            c.column_name AS ''@name'', 
            c.data_type AS ''@data_type'', 
            ISNULL(c.character_maximum_length, '''') AS ''@character_maximum_length''
            --[Current Column Value??]
        FROM INFORMATION_SCHEMA.COLUMNS c
            JOIN ' + @TableName + ' t on
                t.ID = ''' + @PrimaryID + '''
        WHERE TABLE_NAME = ''' + @TableName + ''' and TABLE_SCHEMA=''dbo''
        FOR XML PATH(''column''), ROOT(''' + @TableName + ''')';
PRINT @SQL

When executed produces something like this:

<my_TableName>
  <column name="aName" data_type="int" character_maximum_length="0" />
  <column name="bName" data_type="int" character_maximum_length="0" />
  <column name="cName" data_type="varchar" character_maximum_length="50" />
</my_TableName>

However, I am having trouble, for a given id passed to the sql, to produce something like this:

<my_TableName>
  <column name="aName" data_type="int" character_maximum_length="0">aValue</column>   
  <column name="bName" data_type="int" character_maximum_length="0">bValue</column>
  <column name="cName" data_type="varchar" character_maximum_length="50">cValue</column>
</my_TableName>

Is it possible to alter my current dynamic sql to pull these values and put them into their column name nodes? Are there other steps I could take to produce the above result?

Upvotes: 2

Views: 85

Answers (1)

SoulTrain
SoulTrain

Reputation: 1904

Have you tried this..

select top 0 * FROM table FOR XML AUTO,XMLSCHEMA

This will not get you the same same schema that you are looking for but it does contain all the data you want and is a lot simpler...

Upvotes: 2

Related Questions