MSSQL/Related to XML Query - Microsoft SQL Server

2 New Member

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>

Jun 21 '06 #1

Subscribe Reply

1286

Similar topics

3401

porting phpMyAdmin to MSSQL

by: Gordan | last post by:

Hi, I have a site written in PHP with MS SQL Server 2000 as the db backend. When the site was made there was no need for advanced banner rotation so a 10 line script was written and it served well for years but now there's a need for a much more powerful banner rotation. At first I thought to buy some banner management app but after some time I realized that phpMyAdmin is the best thing. The problem is PMA uses MySQL. I can install...

PHP

1645

MSSQL acting strange

by: Don | last post by:

I have MSSQL2k SP3a on WIN2k SP4. moved a Date/log files to this server about a week ago from a SQL7 server and attached it to this new Sql2k server. everything works fine for about 24hrs and then it starts timing out !! all I have to so is restart the MSSQL service and works fine again till the next day !

Microsoft SQL Server

2463

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 date and time values from given range. The difficulty is mainly caused by the fact, that Date and Time is stored in separate rows as String . I've tried to use CDate() function in my query, but it worked well only during tests on small table (about...

Microsoft Access / VBA

2922

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 MSSQL 2k (SP4) on server 2 (using ASP). I can load a basic 'Hellow world' PHP page hosted on server1 but when I add the code to create a simple connection to MSSQL on server 2, my PHP doesn't seem to connect nor output any of the desired query...

PHP

2660

outer joins in MSSQL

by: mkepick | last post by:

migrated databases from sybase to mssql, migration exported sybase written queries written with the application build in query tool and imported to mssql databases. all db and tables migrated without errors. Regular queries without outer joins work fine but the outer join queries are failing with the following message: Microsoft ODBC SQL Server Driver SQL Server Query contain an outer-join request that is not permitted. I think the MSSQL...

Microsoft SQL Server

8914

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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8820

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

8598

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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8670

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

6223

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5695

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4224

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

2810

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2051

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP