Issues Converting to XML format (for xml explicit)

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)

Issues Converting to XML format (for xml explicit)

Author

Topic

maximation
Starting Member

6 Posts

Posted - 2008-07-15 : 14:27:12

(new to XML Stuff trying to Generate a hierarchy of XML nodes ) I'm exporting out into XML format just like the way I receive but I'm not able to shape it the way I receive it.Below is how I receive the file and this is how I want my output to be like
<ParentEvents>
<ParentEvent>
<TranType>N</TranType>
<Studio>
<StudioID>1001</StudioID>
<Name>My Studio</Name>
<Address>
<Address1>1236 Florida Dr</Address1>
<City>South</City>
<State>Beach</State>
<Zip>21045</Zip>
</Address>
<Phone>214-548-8785</Phone>
<StudioContact>David Thomas</StudioContact>
</Studio>
<OnsiteProducerfirstName> Harold </OnsiteProducerfirstName>
<OnsiteProducerLastName>Campbell</OnsiteProducerLastName>
</ParentEvent>
</ParentEvents>

Instead my output looks like below.
<ParentEvents>
<ParentEvent>
<TranType>N</TranType>
<OnsiteProducerfirstName>Harold</OnsiteProducerfirstName>
<OnsiteProducerLastName>Campbell</OnsiteProducerLastName>
</ParentEvent>
<Studio>
<Studioid>1001</Studioid>
<Name>My Studio</Name>
<Phone>214-548-8785</Phone>
<StudioContact>Very Good AV</StudioContact>
</Studio>
<Address>
<Address1>12 Florida Dr</Address1>
<City>Orlando</City>
<State>Florida</State>
<Zip>21045</Zip>
</Address>
</ParentEvents>
My Query looks like this
SELECT top 1
1 as TAG,
NULL as Parent,
null as [ParentEvents!1!identifier],
null as [ParentEvent!3!TranType!element],
null as [Studio!4!Studioid!element],
null as [Studio!4!Name!element],
null as [Address!2!Address1!element],
null as [Address!2!City!element],
null as [Address!2!State!element],
null as [Address!2!Zip!element],
null as [Studio!4!Phone!element],
null as [Studio!4!StudioContact!element],
null as [ParentEvent!3!OnsiteProducerfirstName!element],
null as [ParentEvent!3!OnsiteProducerLastName!element]
FROM tblParentEvent
UNION ALL
SELECT 3, 1,
intid_pk,
TranType,
null, null,null,null,null,null,null,null,
[OnsiteProducerfirstName].
[OnsiteProducerLastName]
FROM tblParentEvent
UNION ALL
SELECT 4, 1,
intid_pk,
NULL,
Studioid,
name,
null,null,null,null,
[Phone],
[Comments],
NULL, NULL
FROM tblParentEvent
UNION ALL
SELECT 2, 1,
intid_pk,
NULL,NULL,null,
[Address1],
[City],
[State],
Zip],
NULL,NULL,NULL,NULL
FROM tblParentEvent
FOR XML EXPLICIT
Any help would highly be appreciated.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 14:43:16

Have a look at this to understand how to give the node data

http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0901/en/html/dbugen9/00000534.htm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources