MSSQL/Related to XML Query

[mystiqueneha]

Hi All,

I am writinng following query.


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

null as [DestinationAirportCode!2!id!idref],

null as [DestinationAirportCode!2!ConnectingAirportCode!idr ef],

null as [DestinationAirportCode!2!IsFutureFlight!idref] from RouteMap

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

OriginAirportCode as [Origin!1!id],

DestinationAirportCode as [DestinationAirportCode!2!id!idref],

isnull(ConnectingAirportCode, 'NULL ') as [DestinationAirportCode!2!ConnectingAirportCode!idr ef],

IsFutureFlight as [DestinationAirportCode!2!IsFutureFlight!idref]

from RouteMap

order by [Origin!1!id],[DestinationAirportCode!2!id!idref],

[DestinationAirportCode!2!ConnectingAirportCode!idr ef],

[DestinationAirportCode!2!IsFutureFlight!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><Origin id="AMM"><Destination id="AUH" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="BKK" ConnectingAirportCode="AUH" IsFutureFlight="0"/><Destination id="BOM" ConnectingAirportCode="AUH" IsFutureFlight="0"/><Destination id="DAM" ConnectingAirportCode="AUH" IsFutureFlight="0"/><Destination id="ISB" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="MCT" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="MNL" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="YYZ" ConnectingAirportCode="NULL " IsFutureFlight="0"/></Origin><Origin id="AUH"><Destination id="AMM" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="BKK" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="BOM" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="BRU" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="CAI" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="DMM" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="DOH" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="GVA" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="ISB" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="JED" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="KHI" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="KRT" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="LHR" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="MCT" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="MNL" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="MUC" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="OSA" ConnectingAirportCode="AUH" IsFutureFlight="0"/><Destination id="PEW" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="YYZ" ConnectingAirportCode="NULL " IsFutureFlight="0"/></Origin :confused: - <ActualMiles>
:confused: <XML_F52E2B61-18A1-11d1-B105-00805F49916B> :confused: ><Origin id="BKK"><Destination id="AMM" ConnectingAirportCode="AUH" IsFutureFlight="0"/><Destination id="AUH" ConnectingAirportCode="AUH" IsFutureFlight="0"/><Destination id="BOM" ConnectingAirportCode="AUH" IsFutureFlight="0"/><Destination id="DAM" ConnectingAirportCode="AUH" IsFutureFlight="0"/></Origin><Origin id="BOM"><Destination id="AUH" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="DAM" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="ISB" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="MCT" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="MNL" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="YYZ" ConnectingAirportCode="NULL " IsFutureFlight="0"/></Origin><Origin id="DAM"><Destination id="AUH" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="BKK" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="ISB" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="MCT" ConnectingAirportCode="NULL " IsFutureFlight="0"/></Origin><Origin id="ISB"><Destination id="BOM" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="DAM" ConnectingAirportCode="" IsFutureFlight="0"/></Origin><Origin id="MCT"><Destination id="AUH" ConnectingAirportCode="NULL " IsFutureFlight="0"/></Origin><Origin id="MNL"><Destination id="AUH" ConnectingAirportCode="NULL " IsFutureFlight="0"/><Destination id="BKK" ConnectingAirportCode="NULL " IsFutureFlight="0"/></Origin><Origin id="YYZ"><Destination id="BOM" ConnectingAirportCode="NULL " IsFutureFlight="0"/></Origin></XML_F52E2B61-18A1-11d1-B105-00805F49916B>
</RouteMap>