multiple columns to XML column

Question

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>' )

Does the XML need to be in exactly that format? By reusing the element Attribute, but using different attributes, you're providing a different definition for the same name. Could you combine into a single Attribute element with multiple attributes (for the fields), or use an element for EmailAddress, MaxNumberOfConnections, etc? — Dave Simione
– Dave Simione, Commented Oct 4, 2012 at 19:43
XML schema doesn't have to be exactly as the one I show here. — mcl
– mcl, Commented Oct 4, 2012 at 21:31
Then post your exact schema, how then you expect to get an accurrate answer? — Yaroslav
– Yaroslav, Commented Oct 5, 2012 at 6:06

Dave Simione · Accepted Answer · 2012-10-04 19:50:00Z

1

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

answered Oct 4, 2012 at 19:50

Dave Simione

1,4412 gold badges22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

multiple columns to XML column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related