MSSQL/Related to XML Query

[mystiqueneha]

Hi All,

I am writinng following query.


select distinct 1 as tag,
null as parent,
OriginAirportCo de as [Origin!1!id],

null as [DestinationAirp ortCode!2!id!id ref],

null as [DestinationAirp ortCode!2!Conne ctingAirportCod e!idref],

null as [DestinationAirp ortCode!2!IsFut ureFlight!idref] from RouteMap

union all
select distinct 2 as tag, 1 as parent,

OriginAirportCo de as [Origin!1!id],

DestinationAirp ortCode as [DestinationAirp ortCode!2!id!id ref],

isnull(Connecti ngAirportCode, 'NULL ') as [DestinationAirp ortCode!2!Conne ctingAirportCod e!idref],

IsFutureFlight as [DestinationAirp ortCode!2!IsFut ureFlight!idref]

from RouteMap

order by [Origin!1!id],[DestinationAirp ortCode!2!id!id ref],

[DestinationAirp ortCode!2!Conne ctingAirportCod e!idref],

[DestinationAirp ortCode!2!IsFut ureFlight!idref] for xml explicit

Its giving me XML results but ialso Breaking a node .I think this is due to character limit.
Please suggest me to ovewrcome this problem.


<RouteMap>
<XML_F52E2B61-18A1-11d1-B105-00805F49916B><O rigin id="AMM"><Desti nation id="AUH" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="BKK" ConnectingAirpo rtCode="AUH" IsFutureFlight= "0"/><Destination id="BOM" ConnectingAirpo rtCode="AUH" IsFutureFlight= "0"/><Destination id="DAM" ConnectingAirpo rtCode="AUH" IsFutureFlight= "0"/><Destination id="ISB" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="MCT" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="MNL" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="YYZ" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/></Origin><Origin id="AUH"><Desti nation id="AMM" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="BKK" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="BOM" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="BRU" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="CAI" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="DMM" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="DOH" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="GVA" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="ISB" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="JED" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="KHI" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="KRT" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="LHR" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="MCT" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="MNL" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="MUC" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="OSA" ConnectingAirpo rtCode="AUH" IsFutureFlight= "0"/><Destination id="PEW" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="YYZ" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/></Origin :confused: - <ActualMiles>
:confused: <XML_F52E2B61-18A1-11d1-B105-00805F49916B> :confused: ><Origin id="BKK"><Desti nation id="AMM" ConnectingAirpo rtCode="AUH" IsFutureFlight= "0"/><Destination id="AUH" ConnectingAirpo rtCode="AUH" IsFutureFlight= "0"/><Destination id="BOM" ConnectingAirpo rtCode="AUH" IsFutureFlight= "0"/><Destination id="DAM" ConnectingAirpo rtCode="AUH" IsFutureFlight= "0"/></Origin><Origin id="BOM"><Desti nation id="AUH" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="DAM" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="ISB" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="MCT" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="MNL" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="YYZ" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/></Origin><Origin id="DAM"><Desti nation id="AUH" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="BKK" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="ISB" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="MCT" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/></Origin><Origin id="ISB"><Desti nation id="BOM" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="DAM" ConnectingAirpo rtCode="" IsFutureFlight= "0"/></Origin><Origin id="MCT"><Desti nation id="AUH" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/></Origin><Origin id="MNL"><Desti nation id="AUH" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/><Destination id="BKK" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/></Origin><Origin id="YYZ"><Desti nation id="BOM" ConnectingAirpo rtCode="NULL " IsFutureFlight= "0"/></Origin></XML_F52E2B61-18A1-11d1-B105-00805F49916B>
</RouteMap>