May 13, 2011 at 4:54 am
Hi I
n sql server 2008
i had a table log
CREATE TABLE LOG
(
ID int not null identity(1,1),
Sessionnumber varchar(100) null,
LogText varchar(max) not null
)
GO
INSERT INTO LOG
(Sessionnumber, LogText )
VALUES ('D85EAB87-B9B0-4D14-B21C-B39C3C2ECE7F','<?xml version="1.0" encoding="utf-16"?>
<string>
<?xml version="1.0" encoding="utf-16"?>
<Air_SellFromRecommendation xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<messageActionDetails xmlns="http://xml.amadeus.com/ITAREQ_05_2_IA"> <messageFunctionDetails>
<messageFunction>183</messageFunction>
<additionalMessageFunction>M1</additionalMessageFunction>
</messageFunctionDetails>
</messageActionDetails>
<itineraryDetails xmlns="http://xml.amadeus.com/ITAREQ_05_2_IA">
<originDestinationDetails>
<origin>TRV</origin>
</originDestinationDetails>
<segmentInformation>
<travelProductInformation>
<flightDate>
<departureDate>300411
</departureDate>
</flightDate>
<boardPointDetails>
</boardPointDetails>
<flightIdentification>
<flightNumber>3138</flightNumber>
<bookingClass>U</bookingClass>
</flightIdentification>
</travelProductInformation>
<relatedproductInformation>
<statusCode>NN</statusCode>
</relatedproductInformation>
</segmentInformation>
</itineraryDetails>
</Air_SellFromRecommendation>
</string>'),
('D85EAB87-B9B0-4D14-B21C-B39C3C2ECE7F','<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
<s:Header>
<User xmlns="urn:TeknoXML:Flight">XXXXX</User>
<Pass xmlns="urn:TeknoXML:Flight">XXXXX</Pass>
<User-Agent xmlns="urn:TeknoXML:Flight">Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; GTB6.5; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET CLR 1.1.4322)</User-Agent>
<To s:mustUnderstand="1" xmlns="http://schemas.microsoft.com/ws/2005/05/addressing/none"> </To>
</s:Header> <s:Body> <AirGetFlightItinerary xmlns="urn:XXXXML:Flight"> <request><rq:AirAvailRQ xmlns:rq="urn:XXXXML:Flight" Ver="1.0"> <RequestMode>
<!--Max Length : 15-->
<RequestType>Air</RequestType>
<TraceId>Tyriir</TraceId>
<!--Type Air for Airline/Flight-->
<!--Max Length : 30-->
<SearchMode>LowFareAvailability</SearchMode>
<!--Search mode workflow-->
<!--Max Length : 4-->
<IsInternational>true</IsInternational>
<!--Currently for International scenerio-->
<Providers>
<!--Max Length : 2-->
<Provider>1A</Provider>
<!--Providers 1A for Amadeus and 1G for Galileo-->
<Provider>1G</Provider>
<!--Search can be from one or more providers-->
</Providers>
</RequestMode>
<TravelPreferences>
<Flight>
<!--Max Length : 3-->
<From>TRV</From>
<!--Start location-->
<!--Checks from a list of static data-->
<!--Max Length : 3-->
<To>DXB</To>
<!--End location-->
<!--Start location and end location should not be same-->
<!--Checks from a list of static data-->
<!--Max Length : 1-->
<AirLine>9W</AirLine>
<!--Airline number must be two digit code-->
<AirLine>IT</AirLine>
<!--Checks from a list of static data-->
<AirLine>S2</AirLine>
<AirLine>IC</AirLine>
<AirLine>AI</AirLine>
</PreferedAirlines>
</Flight>
</TravelPreferences>
</rq:AirAvailRQ></request>
</AirGetFlightItinerary>
</s:Body>
</s:Envelope>'),
('D85EAB87-B9B0-4D14-B21C-B39C3C2ECE7F','Before Invoke: NULL'),
('D30XXX87-X9X0-4D14-B21C-B39C3C2WWW7F','<?xml version="1.0" encoding="utf-16"?>
<string>
<?xml version="1.0" encoding="utf-16"?>
<Air_SellFromRecommendation xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<messageActionDetails xmlns="http://xml.amadeus.com/ITAREQ_05_2_IA"> <messageFunctionDetails>
<messageFunction>183</messageFunction>
<additionalMessageFunction>M1</additionalMessageFunction>
</messageFunctionDetails>
</messageActionDetails>
<itineraryDetails xmlns="http://xml.amadeus.com/ITAREQ_05_2_IA">
<originDestinationDetails>
<origin>TRV</origin>
</originDestinationDetails>
<segmentInformation>
<travelProductInformation>
<flightDate>
<departureDate>300411
</departureDate>
</flightDate>
<boardPointDetails>
</boardPointDetails>
<flightIdentification>
<flightNumber>3138</flightNumber>
<bookingClass>U</bookingClass>
</flightIdentification>
</travelProductInformation>
<relatedproductInformation>
<statusCode>NN</statusCode>
</relatedproductInformation>
</segmentInformation>
</itineraryDetails>
</Air_SellFromRecommendation>
</string>')
GO
The LogText can be any text it will be different
I have to select the LogText based on Sessionnumber(in the table i had given 2 session number)
**There are two conditions**
1. I have to replace the '<' to '<' and '>' to '>'
2. I have to show the result into xml format
For the first condition i used replace function and this is the code
SELECT REPLACE(REPLACE(LogText,'<','<'),'>','>')
FROM LOG
WHERE [SessionNumber] = 'D85EAB87-B9B0-4D14-B21C-B39C3C2ECE7F'
AND EventText LIKE '<%'
This will replace the character with < and >
When i try to generate the result into xml
SELECT REPLACE(REPLACE(LogText,'<','<'),'>','>')
FROM LOG
WHERE [SessionNumber] = 'D85EAB87-B9B0-4D14-B21C-B39C3C2ECE7F'
FOR XML PATH(''), ROOT ('d'),type
In the xml file the characters '<' and '>' replaced again with the '<', and '>'
i had tried with another code
SELECT Replace(
REPLACE(
(SELECT REPLACE(REPLACE(LogText,'<','<'),'>','>') as'EventText'
FROM LOG
WHERE [SessionNumber] = 'D85EAB87-B9B0-4D14-B21C-B39C3C2ECE7F'
AND EventText LIKE '<%'
FOR XML PATH('')),'<','<'),'>','>')
I want all the log text for a specific session number in xml file
thank you
with regards
DD
May 13, 2011 at 7:43 am
I think what you are looking for is the following
SELECT LogText as [*]
FROM LogFile
FOR XML PATH(''), TYPE
The [*] tells SQL that the data represents XML, so it should retain the < and >, among other things. You can use [data()] for the same purpose. The TYPE says that the final results should also be treated as XML.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply