Need help with complex procedure for sql-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)

Need help with complex procedure for sql-xml

Author

Topic

xoail
Starting Member

9 Posts

Posted - 2008-07-02 : 16:52:03

Hi all,

I am looking for some help in generating an xml file by joining 2 tables in sql db.
Table 1 holds company data which has the following columns:
GV (int), Name (varchar), value (varchar), and Note(varchar)

Table 2 hols the company's financial data for each year and has the following columns:
GV (int), Year (smalldatetime), Name (varchar), value (varchar), and note(varchar).

I need to combine the 2 tables and generate an xml in the following format:


<C>

<A>
<Name></Name>
<Value></value>
<note />
</A>

<year="2000">
<A>
<Name></Name>
<Value></Value>
<note />
</A>
</Year>
<year="2001">
<A>
<Name></Name>
<Value></Value>
<note />
</A>
</Year>
<year>
<A>
<Name></Name>
<Value></Value>
<note />
</A>
</Year>

</C>

I believe we have to use FOR XML EXPLICIT with proper table joining. I need some help with this as I am totally new to sql.
Any help highly appreciated.

Thanks

atulmar
Starting Member

7 Posts

Posted - 2008-07-03 : 14:12:21

Which version of SQL Server you are using.
If sql 2005, then your solution will be very easier.

xoail
Starting Member

9 Posts

Posted - 2008-07-03 : 14:20:27

Yes I am using 2005.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-03 : 14:28:13

This should help you:

http://www.sqlserverandxml.com/2007/12/for-xml-explicit-part-1.html

xoail
Starting Member

9 Posts

Posted - 2008-07-03 : 14:40:23

Yeah but what is given in the link is a basic xml version... what I am looking for is little more complex if you see (or may be its how it looks to me). In my case, there is first a company node 'c' which has demographic data and yearly financial data for 10 years or so. So the structure has to be something like this...
<c>
Company demograph
- year xxxx data
- year yyyy data
.
.
.
so on..
</c>

What makes it complex is the table joining and using explicit properly. I hope some angel will help me ;)

Thanks!

Rajani@30
Starting Member

6 Posts

Posted - 2008-07-04 : 01:26:49

You can probably create a Common Table Expression (CTE)and then use this table to create the XML structure.

xoail
Starting Member

9 Posts

Posted - 2008-07-04 : 01:40:44

Right now I came very close the format using this code:

select
a.Name,
a.Value,
a.Note,
( select
year(b.year) as [@year],
( select
b.name,
b.value,
b.note
for xml path('A'), type
)
from @t2 b
where a.gv = b.gv
for xml path('Year'), type
)
from @t1 a
for xml path('C')

The xml format is almost as I desire...

<C>
  <Name>Company #1</Name>
  <Value>Comp Val #1</Value>
  <Note>Comp Note #1</Note>
  <Year year="2000">
<A>
    <name>Fin 1-#1</name>
    <value>Fin Val 1-#1</value>
    <note>Fin Note 1-#1</note>
</A>
  </Year>
  <Year year="2001">
<A>
    <name>Fin 1-#2</name>
    <value>Fin Val 1-#2</value>
    <note>Fin Note 1-#2</note>
</A>
  </Year>
  <Year year="2002">
<A>    
<name>Fin 1-#3</name>
    <value>Fin Val 1-#3</value>
    <note>Fin Note 1-#3</note>
</A>  
</Year>
  <Year year="2004">
<A>
    <name>Fin 1-#4</name>
    <value>Fin Val 1-#4</value>
    <note>Fin Note 1-#4</note>
<A/>
  </Year>
</C>
<C>
  <Name>Company #2</Name>
  <Value>Comp Val #2</Value>
  <Note>Comp Note #2</Note>
</C>

Now I just want node 'A' to be also encapsulating table1 data like this...

<C>
<A>
  <Name>Company #1</Name>
  <Value>Comp Val #1</Value>
  <Note>Comp Note #1</Note>
</A>
  <Year year="2000">
<A>
    <name>Fin 1-#1</name>
    <value>Fin Val 1-#1</value>
    <note>Fin Note 1-#1</note>
</A>
  </Year>
  <Year year="2001">
<A>
    <name>Fin 1-#2</name>
    <value>Fin Val 1-#2</value>
    <note>Fin Note 1-#2</note>
</A>
  </Year>
  <Year year="2002">
<A>    
<name>Fin 1-#3</name>
    <value>Fin Val 1-#3</value>
    <note>Fin Note 1-#3</note>
</A>  
</Year>
  <Year year="2004">
<A>
    <name>Fin 1-#4</name>
    <value>Fin Val 1-#4</value>
    <note>Fin Note 1-#4</note>
<A/>
  </Year>
</C>
<C>
<A>
  <Name>Company #2</Name>
  <Value>Comp Val #2</Value>
  <Note>Comp Note #2</Note>
</A>
</C>

Anyone who can help get it in that format?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources