Need to rearrange xml data

Question

Need to rearrange xml data

Rahul Bhatnagar

SSC Rookie

Points: 42
More actions
February 4, 2005 at 7:45 am

#165895

I have extracted xml data from sql server tables using the 'for xml auto, elements' clause.
A snapshot of the xml data i have currently-
<CONTACT INFORMATION>
   <NAME> JOHN DOE </NAME>
   <PHONE> 888-888-8888 </PHONE>
   <EMAIL> JOHNDOE@YAHOO.COM </EMAIL>
</CONTACT INFORMATION>
<ADDRESS>
<ADDR_FIRST_LINE> 34 OAKDALE ROAD   </ADDR_FIRST_LINE>
<CITY> NEWARK </CITY>
<STATE> DELAWARE </STATE>
<ZIP> 19713 </ZIP>
</ADDRESS>
I need to rearrange this date so that the address data is part of the contact data
<CONTACT INFORMATION>
   <NAME> JOHN DOE </NAME>
   <PHONE> 888-888-8888 </PHONE>
   <EMAIL> JOHNDOE@YAHOO.COM </EMAIL>

    <ADDRESS>
       <ADDR_FIRST_LINE> 34 OAKDALE ROAD   </ADDR_FIRST_LINE>
       <CITY> NEWARK </CITY>
        <STATE> DELAWARE </STATE>
       <ZIP> 19713 </ZIP>
    </ADDRESS>
</CONTACT INFORMATION>
In other words the address tags and data fall inside the contact onfo tags.

Could someone point me in the right direction as to how to accomplish this.
Thanks in advance ,
Rahul
February 7, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

tal mcmahon-202603 SSC-Addicted Points: 474 More actions · Answer 1

Hi,

I do not have a SQL server in front of me to test this so I am only going to be able to give you a vague direction to go.

I have used the for xml auto and have found that if i rearrange the joins between the tables I can get the XML to nest properly. sometimes that means haveing an extra join that you may not normally use. The other option that I know will work is to use option explicit.

This method you actually tell the Query what to call the elements etc.

first i would play with the joins.

sorry i could not be more descriptive right now.

hth

Tal McMahon

SimonUK Grasshopper Points: 14 More actions · Answer 2

Try searching for 'universal table' in MSDN. In there it explains how to set up the xml.

You might want to try

Create Table #xml(Tag int, Parent int,[Contact_Information!1!Root] char(19), [Detail!2!Name] char(20),[Address!3!FirstLine] vachar(30), [Address!3!City] char(30), [Address!3!State] char(20))

Insert Into #xml(Tag)

Select 1

Insert Into #xml(Tag, Parent,[Detail!2!Name)

Select Distinct 2,1, Name from <TableNameHere>

Select * from #xml <order by statements go here> for xml explicit

Hope this helps

Simon