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.
| Author |
Topic |
|
B Rajeev
Starting Member
5 Posts |
 Posted - 2012-03-14 : 09:08:43
|
| How to form a XML form from the Query?declare @t table(Field1 varchar(50),Field2 varchar(50),x xml)insert @tselect 'TTP,KP','KKR',null union allselect null,null,'<Details><Info MobileNo = "(11)9739466865" Address = "TEST Address" /><Detail Name="Table Info" Field1="GHI HDYGF" Field2="TST RRS"/><Detail Name="Table Info1" Field1="QTP DHYFG" Field2="GRS UYE"/></Details>' union allselect 'KKR,KP',null,'<Details><Info MobileNo = "(11)235345253" Address = "TEST Address1" /><Detail Name="Table Info" Field1="" Field2="MBM SJU"/><Detail Name="Table Info1" Field1="" Field2="GRS MXU"/><Detail Name="Table Info1" Field1="" Field2="BJK VNB"/></Details>' select COALESCE(Field1,REPLACE(x.query('data(/Details/Detail/@Field1)').value('.','varchar(50)'),' ',',')) AS Field1,COALESCE(Field2,REPLACE(x.query('data(/Details/Detail/@Field2)').value('.','varchar(50)'),' ',',')) AS Field2FROM @t Note:- This code gives following result which is attached here with but having commas within the Field content also, but it should return as follows 1) "GHI HDYGF, QTP DHYFG" but it is returning "GHI,HDYGF,QTP DHYFG" (so, many commas) 2) Next Level I need to have <li>GHI HDYGF</li><li>QTP DHYFG</li> Please help me on this.Thanks,Rajeev. |
|
|
|
|
|
|
|
How to form a XML form from the Query?