• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

Using for xml to structre table data as xml

Assume I have the following table in SQL.

module      table_name      column_display
clients      Client Info         ID number
clients      Client Info       First name
clients      Client Info       Last name
clients      Client Info       Mid init
clients      Client Info       SSN
clients      Client Info       Birthdate
clients      Client Info       Sex
clients      Client Info       Race
clients      Client Info        State ID / DL Number
clients      Client Info       ID / DL State
clients      Client Info        Marital Status
clients      Client Info       Follow Up

How can I construct a for xml query that will return the data as xml in the following format:

<clients>
 <table="client info">
      <field name="ID number" />
      <field name="First name" />
      <field name="Last name" />
      <field name="Mid init" />
      <field name="SSN" />
      <field name="Birthdate" />
      <field name="Sex" />
      <field name="Race" />
      <field name="State ID / DL Number" />
      <field name="ID / DL State" />
      <field name="Marital Status" />
      <field name="Follow Up" />
 </table>
</clients>
0
hyphenpipe
Asked:
hyphenpipe
  • 2
1 Solution
 
Neil RussellTechnical Development LeadCommented:
IF you know .NET then you could use the SqlXmlCommand Object


See --> http://msdn.microsoft.com/en-us/library/aa257414(v=sql.80).aspx
0
 
hyphenpipeAuthor Commented:
I am looking for a way to do this from a SQL query such as select....for xml path('')

Thanks.
0
 
pivarCommented:
Hi,

Try this

/peter
select 1 as tag,
		null as parent,
		null [clients!1],
		null [table!2!name],
		null [field!3!name]
from atable 
union 
select 2 as tag,
	   1 as parent,
	   null,
	   table_name,
	   null
from atable 
union 
select 3 as tag,
	   2 as parent,
	   null,
	   null,
	   column_display
from atable 
for xml explicit

Open in new window

0
 
hyphenpipeAuthor Commented:
Perfect solution.

Thanks!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now