SQL Server Performance Forum – Threads Archive
for XML explicit
Hi gurus,<br /><br />i am new to xml. and i am struggling to get the desired results from sql query.<br />my query is like below.<br /><b>select1 as TAG, <br />0 as PARENT ,<br />a.exportid as [atom:entry!1!atom:entryId!hide],<br />a.Operation [atom:entry!1!ycontrol<img src=’/community/emoticons/emotion-3.gif’ alt=’
‘ />peration!element],<br />Null as [partnercontact!2!yjob:name!element],<br />Null as [partnercontact!2!yjob:name!element]<br />from LeeYahooExport a <br />union all<br />select2 as TAG, <br />1 as PARENT,<br />a.exportid, <br />a.operation,<br />b.partner_lname,<br />b.partne_fname<br />from LeeYahooexport a ,leeyahooexportdetail b where a.exportid = b.exportid and a.batchfeedid = b.batchfeedid<br />order by [atom:entry!1!atom:entryId!hide], [partnercontact!2!yjob:name!element] for xml explicit</b><br /><br />The result i am getting is <br /><b><atom:entry><br /><ycontrol<img src=’/community/emoticons/emotion-3.gif’ alt=’‘ />peration><img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />OST </ycontrol<img src=’/community/emoticons/emotion-3.gif’ alt=’‘ />peration><br /><<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />artnercontact><br /><yjob:name>SUBDataLName</yjob:name><br /><yjob:name>SUBDataFName</yjob:name><br /></partnercontact><br /></atom:entry></b><br />But the result i am looking is like below. i tried lot of option but not able to get it.<br /><atom:entry><br /><ycontrol<img src=’/community/emoticons/emotion-3.gif’ alt=’‘ />peration><img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />OST </ycontrol<img src=’/community/emoticons/emotion-3.gif’ alt=’‘ />peration><br /><<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />artnercontact><br /><b><yjob:name type=lname>SUBDataLName</yjob:name><br /><yjob:name type=fname>SUBDataFName</yjob:name></b><br /></partnercontact><br /></atom:entry><br /><br />Thanks in Advance,<br />psk.
See these links are any help:
http://www.eggheadcafe.com/tutorial…8-6f0cacdd9000/sql-server-for-xml-explic.aspx
http://www.revealnet.com/newsletter-v3/0502_B.htm
http://www.15seconds.com/issue/001102.htm
http://sqljunkies.com/Article/296D1B56-8BDD-4236-808F-E62CC1908C4E.scuk
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>