CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
Mark Varnas
Mark Varnas

Reputation: 751

TSQL to get XML data into a column

How can I write query which would output this below?

ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"

DECLARE @x XML;
SET @x = N'<?xml version = "1.0"?>
<EVENT_INSTANCE>
  <EventType>ALTER_TABLE</EventType>
  <PostTime>2015-05-19T14:01:46.930</PostTime>
  <SPID>52</SPID>
  <ServerName>computer1</ServerName>
  <LoginName>domain\user</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>DBA</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>Table_1</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <AlterTableActionList>
    <Create>
      <Columns>
        <Name>c8</Name>
      </Columns>
    </Create>
  </AlterTableActionList>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>ALTER TABLE dbo.Table_1 ADD c8 INT NULL</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>';

Upvotes: 0

Views: 120

Answers (4)

har07
har07

Reputation: 89325

Assuming you want to get string containing all attributes of <SetOption> as output, one possible way is using XQuery for loop like so :

SELECT @x.query('
   for $attr in /EVENT_INSTANCE/TSQLCommand/SetOptions/@*
   (: return value in format : attribute_name="attribute_value" :)
   return concat(local-name($attr),''="'',$attr,''"'')
')

SQL Fiddle

output :

ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"

brief explanation :

  • /EVENT_INSTANCE/TSQLCommand/SetOptions : specify path to <SetOption> element
  • /@* : get all attributes from current context element (it is <SetOption> element in this case)
  • (: some comment here :) : XQuery comment
  • concat() : concatenates all parameters into single string result
  • local-name() : return element local name (a.k.a XML tag name)
  • note that '' within concat() means simply '. Single quotes need to be escaped in this situation, and it is done in SQL Server by doubling each of them.

for reference :

Upvotes: 2

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

Bit of a hack, but this should work:

DECLARE @x XML;
SET @x = N'<?xml version = "1.0"?>
<EVENT_INSTANCE>
  <EventType>ALTER_TABLE</EventType>
  <PostTime>2015-05-19T14:01:46.930</PostTime>
  <SPID>52</SPID>
  <ServerName>computer1</ServerName>
  <LoginName>domain\user</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>DBA</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>Table_1</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <AlterTableActionList>
    <Create>
      <Columns>
        <Name>c8</Name>
      </Columns>
    </Create>
  </AlterTableActionList>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>ALTER TABLE dbo.Table_1 ADD c8 INT NULL</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>';


SELECT REPLACE(REPLACE(CONVERT(VARCHAR(1000), @X.query('(//TSQLCommand/SetOptions)')),'<SetOptions', ''), '/>', '')

Upvotes: 1

DanielG
DanielG

Reputation: 1675

You may also be looking to dynamically generate the event from the current schema. Maybe something down along these lines?

    SELECT
'
<EVENT_INSTANCE>
  <EventType>ALTER_TABLE</EventType>
  <PostTime>2015-05-19T14:01:46.930</PostTime>
  <SPID>52</SPID>
  <ServerName>computer1</ServerName>
  <LoginName>domain\user</LoginName>
  <UserName>' + TABLE_SCHEMA + '</UserName>
  <DatabaseName>DBA</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>' + TABLE_NAME + '</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <AlterTableActionList>
    <Create>
      <Columns>
        <Name>      
        ' 
        +
                (
                SELECT REPLACE(REPLACE(Names, 'lt;', '<name>'), ',&','</name>' + CHAR(13) + CHAR(10)) + CHAR(13) + CHAR(10)  + '</name>' FROM
                (
                SELECT 
                  t1.Table_Name,
                  STUFF((
                    SELECT ',' + '<' + t2.COLUMN_NAME   
                    FROM INFORMATION_SCHEMA.COLUMNS t2
                    WHERE t2.Table_Name = t1.Table_Name
                    FOR XML PATH (''))
                  ,1,2,'') AS Names
                FROM INFORMATION_SCHEMA.COLUMNS t1
                WHERE t1.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME
                GROUP BY t1.Table_Name
                ) I1

                )
        +
        '
        </Name>
      </Columns>
    </Create>
  </AlterTableActionList>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>ALTER TABLE dbo.Table_1 ADD c8 INT NULL</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>'
FROM
    INFORMATION_SCHEMA.TABLES

Upvotes: 0

DanielG
DanielG

Reputation: 1675

I may be completely missing you're situation, but is this what you need Mark:

SELECT 
'
    DECLARE @x XML;
    SET @x = N''<?xml version = "1.0"?>
    <EVENT_INSTANCE>
      <EventType>ALTER_TABLE</EventType>
      <PostTime>2015-05-19T14:01:46.930</PostTime>
      <SPID>52</SPID>
      <ServerName>computer1</ServerName>
      <LoginName>domain\user</LoginName>
      <UserName>dbo</UserName>
      <DatabaseName>DBA</DatabaseName>
      <SchemaName>dbo</SchemaName>
      <ObjectName>Table_1</ObjectName>
      <ObjectType>TABLE</ObjectType>
      <AlterTableActionList>
        <Create>
          <Columns>
            <Name>c8</Name>
          </Columns>
        </Create>
      </AlterTableActionList>
      <TSQLCommand>
        <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
        <CommandText>ALTER TABLE dbo.Table_1 ADD c8 INT NULL</CommandText>
      </TSQLCommand>
    </EVENT_INSTANCE>'';
'

Upvotes: 0

Related Questions