CODEWITHSUNDEEP
sqlsql-servert-sql
mcl
mcl

Reputation: 37

multiple columns to XML column

Given two tables:

T1( EntityID int , EmailAddress varchar(55),MaxNumberOfConnections int )

T2( EntityID int , EntityAttributes XML )

How do I insert all the data from T1 into T2 with a single statement in such a way that all the columns in T1 (all but EntityID ) are converted into one XML column in T2 : 

T1( 1,'[email protected]',454)

T2(1, '<Attributes>
          <Attribute EmailAddress="[email protected]">
          <Attribute MaxNumberOfConnections ="454">
      </Attributes>' )

Upvotes: 0

Views: 2436

Answers (1)

Dave Simione
Dave Simione

Reputation: 1441

Here are two solutions based upon my comment - Single "Attribute" element with multiple attributes:

SELECT
    EntityId,
    (
        SELECT
            EmailAddress AS [Attribute/@EmailAddress],
            MaxNumberOfConnections AS [Attribute/@MaxNumberOfConnections]
        FROM
            T1 i
        WHERE
            i.EntityId = o.EntityId
        FOR XML PATH('Attributes')
    ) AS EntityAttributes 
FROM
    T1 o

Individual element for each field:

SELECT
    EntityId,
    (
        SELECT
            EmailAddress,
            MaxNumberOfConnections
        FROM
            T1 i
        WHERE
            i.EntityId = o.EntityId
        FOR XML PATH('Attributes')
    ) AS EntityAttributes 
FROM
    T1 o

Upvotes: 1

Related Questions