CODEWITHSUNDEEP
sql-serverxml
Rick
Rick

Reputation: 1559

Joining with Temp table outside XML element

I am new to XML+SQL module and I have a code that selects a regular column and a whole bunch of XML data.

Below is my sample code:

create table #temp(cid int, val int)
insert into #temp values
(1,11),
(2,12),
(3,12)

select
    t1.cid,
    xml =
        (
        select t2.cid,t2.val
        from #temp t2
        join #temp t1  on t2.cid = t1.cid
        for xml Path(''), type)
    from #temp t1 

drop table #temp

desired output is:

enter image description here

Rexter link: http://rextester.com/HLZS59752

Any help ??

Upvotes: 1

Views: 29

Answers (2)

Rick
Rick

Reputation: 1559

Thanks @John Cappelletti for that answer. That helped. One mroe solution I found was:

select
    t1.cid,
    xml =
        (
        select t2.cid,t2.val
        from #temp t2
            where t1.cid = t2.cid
        for xml Path(''), type)
    from #temp t1

Instead of join, I added the condition in Where clause and it worked. Updated Rexter link: http://rextester.com/MGXDC39580

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81970

If I understand your question.

Example

select
    t1.cid,
    xml = (Select t1.* for xml path('') )
    from #temp t1

Returns

cid xml
1   <cid>1</cid><val>11</val>
2   <cid>2</cid><val>12</val>
3   <cid>3</cid><val>12</val>   -- Last record in #temp is (3,12)

Upvotes: 1

Related Questions