For Xml Explicit//mssql - Microsoft SQL Server

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 its also Breaking a node .I think this is due to character limit.I don't want that Origin or any other tag should behave like this.
Please suggest me to overcome this problem.

Here is the output.
You can see that XML id is being inserted by the SQL in between.

<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 :mad: </XML_F52E2B61-18A1-11d1-B105-00805F49916B> </ActualMiles> :mad:
- <ActualMiles>
:mad: <XML_F52E2B61-18A1-11d1-B105-00805F49916B> :mad: ><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>

Jun 21 '06 #1

Subscribe Post Reply

2492

Similar topics

PHP+MSSQL

by: badz | last post by:

Hi frenz Lately I try to use MSSQL and PHP , the problem arise when PHP try to read MSSQL field with 'image' data type, header("Content-type: image/jpeg"); // act as a jpg file to browser I...

PHP

PHP MSSQL connection problem.

by: mj | last post by:

Hello, thanks for the help. I am running a WinXP Pro w/ SP2 (my home computer, with ZoneAlarm firewall) Apache 2.0.52 MySQL 4.1.7 PHP 5.1.0-dev I have developed a PHP/MySQL web app that...

PHP

problem : random error with with IIS6 and MSSQL

by: JackTorrance | last post by:

Hi i have a problem with IIS6 and MSSQL and i hope that someone can help me. this is the configuration: Windows 2003 IIS6 MSSQL 7.0 Standard Edition ADO 2.5

Microsoft SQL Server

Queries for MSSQL Server

by: Kukurydz | last post by:

I've got such problem: My database is stored on MSSQL Server. I have to write reports for it in MSAccess. I've got a problem with creating a query which will select records from MSSQL table with...

Microsoft Access / VBA

MSSQL connection from PHP slower when server is remote (crosspost)

by: gharmel | last post by:

I'm trying to get some clues on why I get (much) slower responses from my PHP applications when dealing with a remote sql server as opposed to a local sql server. Here's my situation: Server...

PHP

php5 mssql utf-8 problem

by: ralphie | last post by:

hi all since nearly 2 days i fight with mssql and utf-8 as i need to store and retrieve arabic characters. i tried the com approach ...

PHP

Good solution access MsSQL from ASP [Classic] use Intranet

by: DariuszK | last post by:

I am greeting I would like to consult the better solution(with security consideration) to the access is for MSSQL(2005) from ASP(Classic) in the system of the intranet. a) SQL mixed mode and...

ASP / Active Server Pages

MSSQL 2000 and MSSQL 2005 servers run in the same desktop machine

by: Derftics | last post by:

Hi Guys, Is there anyone who have tried installing MSSQL 2000 and MSSQL 2005 servers in one desktop computer? I have tried using MSDE and successfully install the MSSQL 2000 server but when I...

Microsoft SQL Server

Mssql Iis6

by: guswebb | last post by:

Hi. I'm a newbie to PHP and am having a few problems as follows... I have installed PHP successfully on server 1 which is running IIS 6 (W2k3) and hosting multiple sites, some of which connect to...

PHP

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server