Help to convert columns into XML

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help to convert columns into XML

Author

Topic

sqlilliterate
Starting Member

40 Posts

Posted - 2009-01-01 : 04:35:38

Hi,

I've the following data in the table format... Help me converting this into my expected XML format...


Root	   ParentNode	Node   Name     number  Valid	  Value
----------------------------------------------------------------
Reference       Basic	Book	Book1	1	true	AH.KL.LO
Reference	App	A	App1	1	true	AIK.LPO
Reference	App	A	App2	2	true	JUI.MKJ
Reference	Sub	B	SubA	1	false	LOP.MJH
Reference	Sub	B	SubB	2	false	GTY.JUI
Reference	DI	C	NULL	1	NULL	PLW.KJU

My expected output in xml format is...


<Reference>
  <Basic>
    <Book Name="Book1" number="1" Valid="true">AH.KL.LO</Book>
  </Basic>
  <App>
    <A Name="App1" number="1" Valid="true">AIK.LPO</A>
    <A Name="App2" number="2" Valid="true">JUI.MKJ</A>
  </App>
  <Sub>
    <B Name="SubA" number="1" Valid="false">LOP.MJH</B>
    <B Name="SubB" number="2" Valid="false">GTY.JUI</B>
  </Sub>
  <DI>
    <C number="1">PLW.KJU</C>
  </DI>
</Reference>

Pls help... thanks in advance

Thiyagu_04
Starting Member

37 Posts

Posted - 2009-01-01 : 07:30:39

try with
for xml path()

sqlilliterate
Starting Member

40 Posts

Posted - 2009-01-01 : 07:42:30

Thiyagu, Can u be more elaborate ?
If I use xml path(), am getting individual nodes for all my nodes and attributes, which do not meet my expected format...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-01 : 13:19:11

didnt you get answer here?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=117011

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources