December 11, 2006 at 6:03 am
Hi,
I need to create an XML partly based on the result of a query, the specs call for a tag that contains a Type coded element which has a defined value for each of the codes that can be supplied. An example looks like this:
<Gender tc="1">Male</Gender>
Is there a way to do this in SQL FOR XML EXPLICIT.
Thanks,
Stéphane
December 11, 2006 at 8:44 am
I'm sure there's a nice way to consolidate everthing throwing in some left joins and isnulls (maybe a union or two) but here's a quick example:
Create Table #Employee(EmployeeID int, Employee_Name varchar(50), Employee_GenderTC int)
Insert Into #Employee(EmployeeID, Employee_Name, Employee_GenderTC)
select 1, 'Andrew Butterworth', 1 union all
select 2, 'Barbara Truffle', 2
SELECT Top 0
convert(int, null) As Tag,
convert(int, null) As Parent,
convert(varchar(125), null) As [Employees!1!xmlns],
convert(int, null) AS [Employee!2!id],
convert(varchar(50), null) AS [Employee!2!name],
convert(varchar(7), null) AS [Gender!3],
convert(smallint, null) AS [Gender!3!tc]
Into #Temp
Insert Into #Temp(Tag, Parent, [Employees!1!xmlns])
select 1, null, 'myurl'
Insert Into #Temp(Tag, Parent, [Employee!2!id], [Employee!2!name])
Select 2, 1,
EmployeeID, Employee_Name
From #Employee
Insert Into #Temp(Tag, Parent, [Employee!2!id], [Gender!3], [Gender!3!tc])
Select 3, 2,
EmployeeID,
case Employee_GenderTC when 1 then 'male' else 'female' end,
Employee_GenderTC
From #Employee
Select * From #Temp
Order By [Employees!1!xmlns] DESC, [Employee!2!id], Tag
For XML Explicit
Drop Table #Temp
Drop Table #Employee
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply