8 Replies Latest reply: Aug 30, 2012 5:30 AM by AlexAnd RSS

    SQL Query for reading XML script: Conversion from SQL Server to Oracle

    860250
      Hi,

      I am trying to convert below SQL server query to Oracle & getting error. Please help me on this issue.

      Thanks in advance.

      SELECT     DerivedItems.Timings.value('(ServiceName)[1]','varchar(100)'),
                ROW_NUMBER() OVER(ORDER BY Timings.value('(/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData)[1]', 'varchar(100)')),
      FROM     @MQResponse2.nodes('/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData') as DerivedItems(Timings)

      Oracle Query:

      SELECT XMLTYPE.EXTRACT(VALUE(tt),'/TimingData/ServiceName/text()').getstringval(),
      ROW_NUMBER() OVER(ORDER BY XMLTYPE.EXTRACT(VALUE(tt),'/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData/text()')).getstringval()
      FROM TABLE(XMLSEQUENCE(v_MQResponse2.EXTRACT('//AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData'))) tt;

      Below is the XML script

      <WIP xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><Status><PolicyTypeCd /><DecisioningActivity>POSTFILTER</DecisioningActivity><FilterIndicator>N</FilterIndicator><Channel /><RemovedXMLDeclarations /><LastServiceAttempted>isDeferredTransaction</LastServiceAttempted><ConverseUID>009d291c-cec0-4051-87f9-8672e38bb2cc</ConverseUID><StatusCode>0</StatusCode><Severity>INFO</Severity><StatusDescription>Logging Completed</StatusDescription><MachineName>PVDXESS029-096</MachineName><ConnectionString>Server=PLEXTRN-DV.prodlb.travp.net,40005;Database=PLGatewayA;</ConnectionString><ResponseDateTime>2011-12-16T16:35:55.082</ResponseDateTime><HttpRequestTime>12/16/2011 4:03:55 PM</HttpRequestTime><timeInFLow>1908540</timeInFLow><TimeOutOfFLow>9666</TimeOutOfFLow><TotalTime>1918206</TotalTime><TotalProcessingTime>1918292</TotalProcessingTime><LookupDate>5/23/2010</LookupDate><TransactionType>RFQ1</TransactionType><State>AZ</State><AgentCD>0RV664</AgentCD><PostalCode>85250</PostalCode><County>Maricopa</County><City>Scottsdale</City><Addr1>7319 E KEIM DRIVE</Addr1><LOB>AUTOP</LOB><TransactionEffectiveDt>2012-01-28T00:00:00.00000-05:00</TransactionEffectiveDt></Status><enabledTransactions><FlowTypes><ABSBilling>0</ABSBilling><AutomaticImport>0</AutomaticImport><AutomaticRegistration>0</AutomaticRegistration><BridgeCleaner>0</BridgeCleaner><ContactView>0</ContactView><CoveragePackages>0</CoveragePackages><DoubleImport>0</DoubleImport><EFullCatalog>0</EFullCatalog><EFullfillment>0</EFullfillment><EnabledTransaction>1</EnabledTransaction><GarageTransform>1</GarageTransform><Javacaps>0</Javacaps><MSB>0</MSB><RTCC>0</RTCC><SQIW>1</SQIW><TrimEndorsements>0</TrimEndorsements><VPALS>0</VPALS><GatewayModernization>1</GatewayModernization><GatewayMILogging>1</GatewayMILogging><OpenMedia>0</OpenMedia><BoatService>0</BoatService><ScreenRouting>0</ScreenRouting><Linkage>1</Linkage><EFulfillmentCaching>1</EFulfillmentCaching><GTWLookupCaching>1</GTWLookupCaching><ErrorService>0</ErrorService><SLOLFA>0</SLOLFA><AppendLimitsAndDeductibles>0</AppendLimitsAndDeductibles><PolicyTermChk>1</PolicyTermChk><GIRO>0</GIRO><BillingServiceForDownPayHardEnforce>1</BillingServiceForDownPayHardEnforce><DownPaymentHardEnforcement>0</DownPaymentHardEnforcement><PaymentValidation>0</PaymentValidation><MonthlyPaymentForResponse>0</MonthlyPaymentForResponse><PreFiltersEnabled>0</PreFiltersEnabled><PostFiltersEnabled>1</PostFiltersEnabled><ContactViewImportOnly>0</ContactViewImportOnly><CloseQuote>0</CloseQuote><TargetApplicationRouter>1</TargetApplicationRouter></FlowTypes></enabledTransactions><gatewayServiceCalls><mqdrop><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>5755</TotalTime><Status /><Endpoint>Qmgr=TQ21;SendQ=A4218QA.PLCDE.CICTPLH2.REQUEST.GCM0;RcvQ=A4218QA.GATEWAY.GENERIC.RESPONSE;WaitTime=60;ExpiryTime=300</Endpoint><Primary_deferred /><Response><![CDATA[<AD><InsuranceSvcRs><RqUID>847C9B80-953A-4867-8304-5A2E0F05E10E</RqUID><SPName>QMATIO</SPName><PersAutoPolicyQuoteInqRs><RqUID>EC086D43-756B-4F45-A7F1-D9EF99B9CAC8</RqUID><TransactionResponseDt>2011-12-16</TransactionResponseDt><com.tt_DerivedItems><AgentCd>0RV664</AgentCd><WritingCompanyCd>THM</WritingCompanyCd><MarketCd>A</MarketCd><NumericStateCd>02</NumericStateCd><EffectiveDt>2012-01-28</EffectiveDt><ExpirationDt>2013-01-28</ExpirationDt><FormCd>101</FormCd><PolicySequenceNumber>1</PolicySequenceNumber><PolicyTerm>12</PolicyTerm><TotalPremiumAmt>1329.00</TotalPremiumAmt><RatingPlan>9</RatingPlan><ReportingOffice>183</ReportingOffice><AlphaStateCd>AZ</AlphaStateCd><ProcessDt>2012-01-28 12:00:00.000</ProcessDt><PricingTier>028</PricingTier><TotalPremiumAndTaxAmt>1329.00</TotalPremiumAndTaxAmt><ProgramCd>QU</ProgramCd><OriginalQuoteDt>2011-12-15</OriginalQuoteDt><CustomerNumber>0000039176213</CustomerNumber><PricingTrack>TR028</PricingTrack><PrrInsLpseCd>N</PrrInsLpseCd><ServiceOffice>476</ServiceOffice><PremiumInfo><PremiumCd>PIF</PremiumCd><PremiumAmt>1329.00</PremiumAmt></PremiumInfo><PremiumInfo><PremiumCd>INS</PremiumCd><PremiumAmt>1400.00</PremiumAmt></PremiumInfo><TimingData><ServiceName>XDX10021-BLD-FDS:SFGA0001</ServiceName><StartTime>2011121616045588-0500</StartTime><EndTime>2011121616045600-0500</EndTime></TimingData><TimingData><ServiceName>XDX10042-TRN-FDS:SFGA0001</ServiceName><StartTime>2011121616045600-0500</StartTime><EndTime>2011121616045678-0500</EndTime></TimingData><TimingData><ServiceName>IGA1071V-ADD-GW-CUST:</ServiceName><StartTime>2011121616045638-0500</StartTime><EndTime>2011121616045644-0500</EndTime></TimingData><TimingData><ServiceName>IOE1023V-SUMMARY-REQUEST</ServiceName><StartTime>2011121616045658-0500</StartTime><EndTime>2011121616045659-0500</EndTime></TimingData><TimingData><ServiceName>IOE1022V-CRDT-REQ-CK</ServiceName><StartTime>2011121616045659-0500</StartTime><EndTime>2011121616045662-0500</EndTime></TimingData><TimingData><ServiceName>IOE1002V-REPORT-VIEW-S</ServiceName><StartTime>2011121616045662-0500</StartTime><EndTime>2011121616045662-0500</EndTime></TimingData><TimingData><ServiceName>XDX10092-MAP-FDS:SFGA0001</ServiceName><StartTime>2011121616045678-0500</StartTime><EndTime>2011121616045682-0500</EndTime></TimingData><TimingData><ServiceName>M3172F00-CALLCARS:PRICE</ServiceName><StartTime>2011121616045685-0500</StartTime><EndTime>2011121616045812-0500</EndTime></TimingData><TimingData><ServiceName>M3172F00-CALLCARS:QUOTE</ServiceName><StartTime>2011121616045814-0500</StartTime><EndTime>2011121616045892-0500</EndTime></TimingData><TimingData><ServiceName>XDX10021-BLD-FDS:SFGA0002</ServiceName><StartTime>2011121616045893-0500</StartTime><EndTime>2011121616045914-0500</EndTime></TimingData><TimingData><ServiceName>XDX10042-TRN-FDS:SFGA0002</ServiceName><StartTime>2011121616045914-0500</StartTime></TimingData><PLWQ_QueWriteTmstp>2011-12-16-16.04.58.934241</PLWQ_QueWriteTmstp><PolicyProcessingEvent>POST RATING</PolicyProcessingEvent><ThirdPartyReports><CategoryCode>CREDIT</CategoryCode><InstanceId>324876227541053</InstanceId></ThirdPartyReports><AlphaInsuranceScore>AANH</AlphaInsuranceScore><InsuranceScoreSourceCd>T</InsuranceScoreSourceCd></com.tt_DerivedItems><PersPolicy><LOBCd>AUTOP</LOBCd><ContractTerm><EffectiveDt>2012-01-28</EffectiveDt><DurationPeriod><NumUnits>12</NumUnits></DurationPeriod></ContractTerm><CreditScoreInfo><CSSourceCd>T</CSSourceCd></CreditScoreInfo><QuoteInfo><CompanysQuoteNumber>1387853026</CompanysQuoteNumber><com.tt_CustomerNumber>0000039176213</com.tt_CustomerNumber><com.tt_CICSRegion>CICTPLH2</com.tt_CICSRegion><com.tt_IFSTokenId>223207027541053</com.tt_IFSTokenId><com.tt_IFSVendorCd>DMS</com.tt_IFSVendorCd></QuoteInfo></PersPolicy><PersAutoLineBusiness><LOBCd>AUTOP</LOBCd><Coverage><CoverageDesc>Safe Driver Advantage</CoverageDesc><CreditOrSurcharge id="CS291762915061053"><CreditSurchargeCd>com.tt_SafeDriverAdvantage</CreditSurchargeCd></CreditOrSurcharge></Coverage><Coverage><CoverageDesc>Early Quote Advantage</CoverageDesc><CreditOrSurcharge id="CS514762915061053"><CreditSurchargeCd>com.tt_EarlyQuoteAdvantage</CreditSurchargeCd></CreditOrSurcharge></Coverage><PersVeh id="VEH1"><Manufacturer>NISSA</Manufacturer><Model>SENTRA E/X</Model><ModelYear>1994</ModelYear><VehIdentificationNumber>1N4EB31F6RC817270</VehIdentificationNumber><Coverage><CoverageCd>PD</CoverageCd><CurrentTermAmt><Amt>283.00</Amt></CurrentTermAmt></Coverage><Coverage><CoverageCd>BI</CoverageCd><CurrentTermAmt><Amt>469.00</Amt></CurrentTermAmt></Coverage><Coverage><CoverageCd>MEDPM</CoverageCd><CurrentTermAmt><Amt>126.00</Amt></CurrentTermAmt></Coverage><Coverage><CoverageCd>UM</CoverageCd><CurrentTermAmt><Amt>91.00</Amt></CurrentTermAmt></Coverage><Coverage><CoverageCd>UNDUM</CoverageCd><CurrentTermAmt><Amt>40.00</Amt></CurrentTermAmt></Coverage><Coverage><CoverageCd>COMP</CoverageCd><CurrentTermAmt><Amt>185.00</Amt></CurrentTermAmt></Coverage><Coverage><CoverageCd>COLL</CoverageCd><CurrentTermAmt><Amt>135.00</Amt></CurrentTermAmt></Coverage></PersVeh></PersAutoLineBusiness><PolicySummaryInfo><PolicyStatusCd>QUOTEDNOTBOUND</PolicyStatusCd><FullTermAmt><Amt>1329.00</Amt></FullTermAmt><com.tt_AlternatePremium>1400.00</com.tt_AlternatePremium><com.tt_PrimaryPremiumCd>PIF</com.tt_PrimaryPremiumCd><com.tt_AlternatePremiumCd>FULL</com.tt_AlternatePremiumCd></PolicySummaryInfo><MsgStatus><MsgStatusCd>SUCCESS</MsgStatusCd></MsgStatus><RemarkText id="MSG001" IdRef="MSG001"><ProbLog>NO TEXT FOUND FOR MESSAGE CODE</ProbLog><ProbCode>000000306</ProbCode></RemarkText></PersAutoPolicyQuoteInqRs></InsuranceSvcRs></AD>]]></Response><TotalMQSendTime>0</TotalMQSendTime><TotalMQReceTime>0</TotalMQReceTime></mqdrop><autoRegistration><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://theadaw1:21071/authorizedconnectionservices.asmx</Endpoint><Primary_deferred /><Response>false</Response></autoRegistration><absEmail><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Primary_deferred /></absEmail><msb><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://theadaw1.dev.prod.travp.net:20206/RequestMsbWebService.asmx</Endpoint><Primary_deferred /><ReplacedCoverageA /><ReplacementCost /></msb><wsRouter><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://integration1jas-strat.dvllb.travp.net/RouterWS/services/RouterWS</Endpoint><Primary_deferred /><RouterCallType>CACHED</RouterCallType></wsRouter><javacapsCall><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://integration1piservices.prodlb.travp.net/plGatewayAuto/ExecutePortType</Endpoint><Primary_deferred /></javacapsCall><rtccCall><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://int1-strat-bs.dvllb.travp.net/BillingWS/services/BillingWS</Endpoint><Primary_deferred /><Response /><BrokerTransactionID /><BrokerName /><InternalTransactionID /></rtccCall><tranManLogRequest><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>354261449</startTime><endTime>354261450</endTime><TotalTime>1</TotalTime><Status>SUCCESS</Status><Endpoint>Server=PLEXTRN-DV.prodlb.travp.net,40005;Database=PLGatewayA;</Endpoint><Primary_deferred>Deferred</Primary_deferred></tranManLogRequest><tranManIsEnabled><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>354261460</startTime><endTime>354261745</endTime><TotalTime>285</TotalTime><Status>SUCCESS</Status><Endpoint>Server=PLEXTRN-DV.prodlb.travp.net,40005;Database=PLGatewayA;</Endpoint><Primary_deferred>Primary</Primary_deferred><FromCache>0</FromCache></tranManIsEnabled><vpals><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://integration1piservices.prodlb.travp.net/VPALSService/services/VPALSService</Endpoint><Primary_deferred /></vpals><bridgeCleaning><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint /><Primary_deferred /></bridgeCleaning><XMLValidation><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>354262334</startTime><endTime>354262899</endTime><TotalTime>565</TotalTime><Status>SUCCESS</Status><Endpoint /><Primary_deferred>Primary</Primary_deferred></XMLValidation><coveragePackages><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint /><Primary_deferred /></coveragePackages><sqiwcall><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>356179577</startTime><endTime>356179590</endTime><TotalTime>13</TotalTime><Status>SUCCESS</Status><Endpoint>Qmgr=TQ21;SendQ=A4218QA.GATEWAY.QUOTE.TO.QIW;RcvQ=A4218QA.GATEWAY.QUOTE.TO.QIW;WaitTime=60;ExpiryTime=300</Endpoint><Primary_deferred>Deferred</Primary_deferred><SqiwXML><![CDATA[<c6770gwy-gwy-mq-rcd><gwy-h-timestamp><gwy-h-date>2011-12-16</gwy-h-date><gwy-h-time>16:35:55.8328750</gwy-h-time></gwy-h-timestamp><gwy-h-lob>AUTO</gwy-h-lob><gwy-h-origination-code>GA</gwy-h-origination-code><hdr-filler></hdr-filler><bus-cntr-cust-num-nme>0000039176213</bus-cntr-cust-num-nme><account-pol></account-pol><form-pol>101</form-pol><seq-pol>1</seq-pol><vehst-alpha-pol>AZ</vehst-alpha-pol><control-agent-pol>0RV664</control-agent-pol><source-partner-nm>COM.QUOMATION</source-partner-nm><quote-origin>CallCenter</quote-origin><package-id></package-id><package-name></package-name><signon-role-cd>RFQ1</signon-role-cd><PLWQ_QueWriteTmstp>2011-12-16-16.04.58.934241</PLWQ_QueWriteTmstp><user-id></user-id><session-id>1387853026</session-id><request-id>EC086D43-756B-4F45-A7F1-D9EF99B9CAC8</request-id><iscr-token-id>223207027541053</iscr-token-id><mvr-token-id></mvr-token-id><converse-user-id>009d291c-cec0-4051-87f9-8672e38bb2cc</converse-user-id><consent-to-ordr-ind>1</consent-to-ordr-ind><incident-usage-cd></incident-usage-cd><prr-pol-exp-dt>2012-01-28</prr-pol-exp-dt><call-flow-type></call-flow-type><email-addr>ABI@ABC.COM</email-addr><machine-id></machine-id><channel-id></channel-id><monthly-premium></monthly-premium><fltr-ind>N</fltr-ind><decn-act-cd>POSTFILTER</decn-act-cd><trm-eff-dt>2012-01-28</trm-eff-dt><data-filler></data-filler></c6770gwy-gwy-mq-rcd>]]></SqiwXML><DoubleRateSqiwXML><![CDATA[]]></DoubleRateSqiwXML></sqiwcall><efullpackagecatalog><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://integration2piw.prodlb.travp.net/Efulfillment/EfulfillmentServices</Endpoint><Primary_deferred /><autoIDCardInd /><eSignInd /><Code /><Desc /><CallingApp /><SPName /><FromCache /></efullpackagecatalog><efullprocess><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://integration2piw.prodlb.travp.net/Efulfillment/EfulfillmentServices</Endpoint><Primary_deferred /><Code /><Desc /><AgentCd /><FormCode /></efullprocess><garageTransform><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>354262327</startTime><endTime>354262331</endTime><TotalTime>4</TotalTime><Status>SUCCESS</Status><Endpoint /><Primary_deferred>Primary</Primary_deferred></garageTransform><CRDBaddProspect><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://theadaw1.dev.prod.travp.net:21071/crdbservices.asmx</Endpoint><Primary_deferred /></CRDBaddProspect><CVDBimportQuote><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://theadaw1.dev.prod.travp.net:20204/ContactViewServices.asmx</Endpoint><Primary_deferred /><AccountCode /><UserId /></CVDBimportQuote><Milsubmitter><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://integrationsiw.prodlb.travp.net/MILSubmitterWeb/services/InteractionSubmitter</Endpoint><Primary_deferred /></Milsubmitter><VRUCommonUtil><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://theacaw1.dev.prod.travp.net:20208/GenesysConfig.asmx</Endpoint><Primary_deferred /></VRUCommonUtil><EmailService><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint /><Primary_deferred /></EmailService><boatService><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://THEASAW1.dev.prod.travp.net:21090/GRateSrv.asmx</Endpoint><Primary_deferred /></boatService><linkageService><ErrorInfo>2058:Queue manager name not valid or not known.</ErrorInfo><startTime>356179593</startTime><endTime>356179598</endTime><TotalTime>5</TotalTime><Status>ERROR</Status><Endpoint>Qmgr=*PIMIT;SendQ=A3105QA.PIMI.RT2MI.DEV;RcvQ=A3105QA.PIMI.RT2MI.DEV;WaitTime=60;ExpiryTime=300</Endpoint><Primary_deferred>Deferred</Primary_deferred><Request><![CDATA[<PIPolicy><messageHeader><messageId>POST RATING2011-12-16T16.04.58.934241-05:00</messageId><messageWriteDate>2011-12-16T16:35:55.848500-05:00</messageWriteDate></messageHeader><applicationInfo><sourceApplicationCd>GTW</sourceApplicationCd><transactionType>NBQT</transactionType><transactionDate>2011-12-16T16.04.58.934241-05:00</transactionDate><policyProcessingEvent>POST RATING</policyProcessingEvent></applicationInfo><producerInfo><reportingOfficeCd>183</reportingOfficeCd></producerInfo><personalPolicy><policyAccountInfo><customer><customerNumber>0000039176213</customerNumber></customer><policyContract><policyEffectiveDate>2012-01-28</policyEffectiveDate><policyExpirationDate>2013-01-28</policyExpirationDate></policyContract></policyAccountInfo><productInfo><ratingStateCd>AZ</ratingStateCd><lineOfBusiness>AUTO</lineOfBusiness><policyFormCd>101</policyFormCd><ratingPlan>9</ratingPlan></productInfo><policyInfo></policyInfo></personalPolicy><thirdPartyData><thirdPartyReports><report><categoryCd>CREDIT</categoryCd><requestTokenId>223207027541053</requestTokenId><reportInstanceId>324876227541053</reportInstanceId></report></thirdPartyReports></thirdPartyData></PIPolicy>]]></Request><DoubleRateXml><![CDATA[]]></DoubleRateXml><LinkageCall>1</LinkageCall></linkageService><errorService><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status></Status><Endpoint>http://unitjas.prodlb.travp.net/UserRegistration/services/trav.ws</Endpoint><Primary_deferred></Primary_deferred></errorService><downPayHardEnforcement><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>356172264</startTime><endTime>356178325</endTime><TotalTime>6061</TotalTime><Status>SUCCESS</Status><Endpoint /><Primary_deferred>Primary</Primary_deferred><DatabaseReadWriteTime>0</DatabaseReadWriteTime></downPayHardEnforcement><billingService><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>356173783</startTime><endTime>356177694</endTime><TotalTime>3911</TotalTime><Status>SUCCESS</Status><Endpoint>http://unit3jas.prodlb.travp.net/BillingService/services/trav.ws</Endpoint><Primary_deferred>Primary</Primary_deferred></billingService><commandOrchestrator><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint /><Primary_deferred /></commandOrchestrator><coveragePackageService><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint /><Primary_deferred /></coveragePackageService><creditCardValidationsvc><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint /><Primary_deferred /></creditCardValidationsvc><bankRouteNumValidationsvc><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint /><Primary_deferred /></bankRouteNumValidationsvc><filtersOrchestration><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>354262921</startTime><endTime>356172262</endTime><TotalTime>1909341</TotalTime><Status>SUCCESS</Status><Endpoint /><Primary_deferred>Primary</Primary_deferred><CustomerLookupCustomer /><HostCustomer>0000039176213</HostCustomer><FilterIndicator>N</FilterIndicator><DecisioningActivity>POSTFILTER</DecisioningActivity></filtersOrchestration><importByPass><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint /><Primary_deferred /></importByPass><importSystem><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint /><Primary_deferred /></importSystem></gatewayServiceCalls><FinalAD><![CDATA[<AD><SignonRq><SignonTransport><SignonRoleCd>RFQ1</SignonRoleCd><CustId><SPName>COM.QUOMATION</SPName><CustPermId>0a0165</CustPermId><CustLoginId>CallCenter</CustLoginId><com.tt_ToolUserId>PROD\gmyers</com.tt_ToolUserId></CustId></SignonTransport><ClientDt>2011-12-15</ClientDt><CustLangPref>English</CustLangPref><ClientApp><Org>CALLCENTER</Org><Name>QUOMATION.COM</Name><Version>11.11.1</Version></ClientApp></SignonRq><InsuranceSvcRq><com.quomation_TransactionId>1980624D-2015-4D2D-9694-31A9DDD0D1D3</com.quomation_TransactionId><RqUID>847C9B80-953A-4867-8304-5A2E0F05E10E</RqUID><PersAutoPolicyQuoteInqRq><RqUID>EC086D43-756B-4F45-A7F1-D9EF99B9CAC8</RqUID><TransactionRequestDt>2012-01-28T00:00:00.00000-05:00</TransactionRequestDt><TransactionEffectiveDt>2012-01-28T00:00:00.00000-05:00</TransactionEffectiveDt><com.tt_OriginalQuoteDt>2011-12-15</com.tt_OriginalQuoteDt><CurCd>USAD</CurCd><Producer><ProducerInfo><ContractNumber>0RV664</ContractNumber></ProducerInfo></Producer><InsuredOrPrincipal id="FNI"><GeneralPartyInfo><NameInfo><PersonName><Surname>TESTING</Surname><GivenName>TESTAZ</GivenName></PersonName></NameInfo><Communications><PhoneInfo><CommunicationUseCd>Home</CommunicationUseCd><PhoneNumber>+1-520-9475471</PhoneNumber></PhoneInfo><EmailInfo><EmailAddr>ABI@ABC.COM</EmailAddr></EmailInfo></Communications></GeneralPartyInfo></InsuredOrPrincipal><PersPolicy><LOBCd>AUTOP</LOBCd><CreditScoreInfo><com.tt_CreditConsentToOrderInd>1</com.tt_CreditConsentToOrderInd></CreditScoreInfo><ContractTerm><EffectiveDt>2011-12-25</EffectiveDt><DurationPeriod><NumUnits>12</NumUnits><UnitMeasurementCd>Months</UnitMeasurementCd></DurationPeriod></ContractTerm><OtherOrPriorPolicy><PolicyCd>Prior</PolicyCd><LOBCd>AUTOP</LOBCd><NAICCd>12963</NAICCd><ContractTerm><ExpirationDt>2012-01-28</ExpirationDt></ContractTerm><LengthTimeWithPreviousInsurer><NumUnits>48</NumUnits><UnitMeasurementCd>M</UnitMeasurementCd></LengthTimeWithPreviousInsurer><Coverage><CoverageCd>BI</CoverageCd><Limit><FormatCurrencyAmt><Amt>25000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PerPerson</LimitAppliesToCd></Limit><Limit><FormatCurrencyAmt><Amt>50000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PerAcc</LimitAppliesToCd></Limit></Coverage><com.tt_6_Mons_Continuous_Cov>Y</com.tt_6_Mons_Continuous_Cov></OtherOrPriorPolicy><QuoteInfo><CompanysQuoteNumber>1387853026</CompanysQuoteNumber><com.tt_MachineName>127.0.0.1</com.tt_MachineName><com.tt_RemoteMachineName>127.0.0.1</com.tt_RemoteMachineName><com.tt_RequestTime>2011-12-16T16:03:55.567</com.tt_RequestTime><com.tt_Channel></com.tt_Channel><com.tt_FormCode>101</com.tt_FormCode><com.tt_PolicyState>AZ</com.tt_PolicyState><com.tt_BookTransferIndicator></com.tt_BookTransferIndicator><com.tt_AutomatedImportIndicator></com.tt_AutomatedImportIndicator><com.tt_RegistrationWorkgroupCode></com.tt_RegistrationWorkgroupCode><com.tt_ConverseUid>009d291c-cec0-4051-87f9-8672e38bb2cc</com.tt_ConverseUid><com.tt_TestTransactionCycleDate>2012-01-28T00:00:00.00000-05:00</com.tt_TestTransactionCycleDate><com.tt_TransactionTypeCd>RFQ1</com.tt_TransactionTypeCd></QuoteInfo><QuestionAnswer><QuestionCd>ContinuouslyInsured</QuestionCd><YesNoCd>Y</YesNoCd><Explanation></Explanation></QuestionAnswer><QuestionAnswer><QuestionCd>VehiclePriorDamage</QuestionCd><YesNoCd>N</YesNoCd></QuestionAnswer><QuestionAnswer><QuestionCd>PE01</QuestionCd><YesNoCd>N</YesNoCd></QuestionAnswer><PersApplicationInfo><InsuredOrPrincipal><GeneralPartyInfo><Addr><AddrTypeCd>StreetAddress</AddrTypeCd><Addr1>7319 E KEIM DRIVE</Addr1><City>Scottsdale</City><StateProvCd>AZ</StateProvCd><PostalCode>85250</PostalCode><County>Maricopa</County></Addr><Addr><AddrTypeCd>MailingAddress</AddrTypeCd><Addr1>4321 Monsoon Trail</Addr1><City>Tucson</City><StateProvCd>AZ</StateProvCd><PostalCode>85749</PostalCode><County>Pima</County></Addr><Addr><AddrTypeCd>PreviousAddress</AddrTypeCd><Addr1>1615 N DELAWARE DR  88</Addr1><Addr2></Addr2><City>APACHE JUNCTION</City><StateProvCd>AZ</StateProvCd><PostalCode>85220</PostalCode></Addr></GeneralPartyInfo></InsuredOrPrincipal><ResidenceTypeCd>DW</ResidenceTypeCd><ResidenceOwnedRentedCd>Y</ResidenceOwnedRentedCd></PersApplicationInfo><DriverVeh DriverRef="Drv1" VehRef="Veh1"><UsePct>100</UsePct></DriverVeh></PersPolicy><PersAutoLineBusiness><LOBCd>AUTOP</LOBCd><PersDriver id="Drv1"><GeneralPartyInfo><NameInfo><PersonName><Surname>TESTING</Surname><GivenName>TESTAZ</GivenName><NameSuffix></NameSuffix></PersonName><TaxIdentity><TaxIdTypeCd>SSN</TaxIdTypeCd><TaxId>#####1111</TaxId></TaxIdentity></NameInfo></GeneralPartyInfo><DriverInfo><PersonInfo><GenderCd>M</GenderCd><BirthDt>1987-07-07</BirthDt><MaritalStatusCd>M</MaritalStatusCd><LengthTimeEmployed><NumUnits>52</NumUnits><UnitMeasurementCd>M</UnitMeasurementCd></LengthTimeEmployed><LengthTimeCurrentOccupation><NumUnits>52</NumUnits><UnitMeasurementCd>M</UnitMeasurementCd></LengthTimeCurrentOccupation><OccupationClassCd>EXE</OccupationClassCd></PersonInfo><License><StateProvCd>AZ</StateProvCd><LicensedDt>2004-04-07</LicensedDt><LicensePermitNumber>A11111111</LicensePermitNumber></License><QuestionAnswer><QuestionCd>ForeignLicense</QuestionCd><YesNoCd>N</YesNoCd></QuestionAnswer><QuestionAnswer><QuestionCd>PE40</QuestionCd><YesNoCd>N</YesNoCd><Explanation>Valid</Explanation></QuestionAnswer><QuestionAnswer><QuestionCd>PE24</QuestionCd><YesNoCd>N</YesNoCd></QuestionAnswer></DriverInfo><PersDriverInfo><DriverRelationshipToApplicantCd>IN</DriverRelationshipToApplicantCd><DistantStudentInd>N</DistantStudentInd><FinancialResponsibilityFiling><FilingCd>N</FilingCd></FinancialResponsibilityFiling><GoodStudentCd>N</GoodStudentCd></PersDriverInfo></PersDriver><PersVeh id="Veh1"><Manufacturer>NISSAN</Manufacturer><Model>SENTRA E/XE/SE/GXE 1.6L</Model><ModelYear>1994</ModelYear><VehBodyTypeCd>PP</VehBodyTypeCd><CostNewAmt><Amt>12299</Amt></CostNewAmt><Registration><StateProvCd>AZ</StateProvCd></Registration><EstimatedAnnualDistance><NumUnits>17000</NumUnits><UnitMeasurementCd>Miles</UnitMeasurementCd></EstimatedAnnualDistance><VehIdentificationNumber>1N4EB31F6RC817270</VehIdentificationNumber><VehSymbolCd>11</VehSymbolCd><AntiLockBrakeCd>N</AntiLockBrakeCd><GaragingCd>G</GaragingCd><OdometerReading><NumUnits>289000</NumUnits><UnitMeasurementCd>Miles</UnitMeasurementCd></OdometerReading><VehUseCd>PL</VehUseCd><SeatBeltTypeCd>Active</SeatBeltTypeCd><AirBagTypeCd>Driver</AirBagTypeCd><AdditionalInterest><AdditionalInterestInfo><NatureInterestCd>LOSSP</NatureInterestCd></AdditionalInterestInfo><GeneralPartyInfo><NameInfo><CommlName><CommercialName>ALBERT</CommercialName></CommlName></NameInfo><Addr><Addr1>5465 HEW WOOD</Addr1><City>SALT LAKE CITY</City><StateProvCd>UT</StateProvCd><PostalCode>84118</PostalCode></Addr></GeneralPartyInfo></AdditionalInterest><Coverage><CoverageCd>BI</CoverageCd><Limit><FormatCurrencyAmt><Amt>25000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PerPerson</LimitAppliesToCd></Limit><Limit><FormatCurrencyAmt><Amt>50000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PerAcc</LimitAppliesToCd></Limit></Coverage><Coverage><CoverageCd>PD</CoverageCd><Limit><FormatCurrencyAmt><Amt>15000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PropDam</LimitAppliesToCd></Limit></Coverage><Coverage><CoverageCd>MEDPM</CoverageCd><Limit><FormatCurrencyAmt><Amt>2000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PerPerson</LimitAppliesToCd></Limit></Coverage><Coverage><CoverageCd>UM</CoverageCd><Limit><FormatCurrencyAmt><Amt>25000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PerPerson</LimitAppliesToCd></Limit><Limit><FormatCurrencyAmt><Amt>50000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PerAcc</LimitAppliesToCd></Limit></Coverage><Coverage><CoverageCd>UNDUM</CoverageCd><Limit><FormatCurrencyAmt><Amt>25000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PerPerson</LimitAppliesToCd></Limit><Limit><FormatCurrencyAmt><Amt>50000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PerAcc</LimitAppliesToCd></Limit></Coverage><Coverage><CoverageCd>COMP</CoverageCd><Deductible><FormatCurrencyAmt><Amt>ACV</Amt></FormatCurrencyAmt><DeductibleTypeCd>USAD</DeductibleTypeCd><DeductibleAppliesToCd>AllPeril</DeductibleAppliesToCd></Deductible></Coverage><Coverage><CoverageCd>COLL</CoverageCd><Deductible><FormatCurrencyAmt><Amt>500</Amt></FormatCurrencyAmt><DeductibleTypeCd>USAD</DeductibleTypeCd><DeductibleAppliesToCd>AllPeril</DeductibleAppliesToCd></Deductible></Coverage><VehicleLeasedCd>F</VehicleLeasedCd></PersVeh><NumLicensed>1</NumLicensed></PersAutoLineBusiness></PersAutoPolicyQuoteInqRq></InsuranceSvcRq></AD>]]></FinalAD></WIP>

      Edited by: 857247 on Aug 28, 2012 7:00 PM
        • 1. Re: SQL Query for reading XML script: Conversion from SQL Server to Oracle
          860250
          Can anyone help me on this please.
          • 2. Re: SQL Query for reading XML script: Conversion from SQL Server to Oracle
            odie_63
            Hi,

            OK, so now you're posting here? Why not stay on the {forum:id=34} forum?
            Oracle Query:

            SELECT XMLTYPE.EXTRACT(VALUE(tt),'/TimingData/ServiceName/text()').getstringval(),
            ROW_NUMBER() OVER(ORDER BY XMLTYPE.EXTRACT(VALUE(tt),'/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData/text()')).getstringval()
            FROM TABLE(XMLSEQUENCE(v_MQResponse2.EXTRACT('//AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData'))) tt;
            We already showed you how to use XMLTABLE function, yet you insist on deprecated methods.
            This one is no different than before :
            SELECT x2.*
            FROM XMLTable(
                   '/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData'
                   passing v_MQResponse2
                   columns service_name varchar2(30) path 'ServiceName'
                         , start_time   varchar2(30) path 'StartTime'
                         , end_time     varchar2(30) path 'EndTime'
                 ) x2
            ;
            I'm not sure which column(s) you want to apply ROW_NUMBER() on, so I'll let that part to you.

            Hope that helps.
            • 3. Re: SQL Query for reading XML script: Conversion from SQL Server to Oracle
              860250
              This was helpful to me. I wanted conversion for row_number() used in below query(SQL Server). From the query I can see it is used on TimingData. so please let me know on how to use.

              Thanks in advance

              SELECT     DerivedItems.Timings.value('(ServiceName)[1]','varchar(100)'),
              ROW_NUMBER() OVER(ORDER BY Timings.value('(/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData)[1]', 'varchar(100)')),
              FROM     @MQResponse2.nodes('/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData') as DerivedItems(Timings)
              • 4. Re: SQL Query for reading XML script: Conversion from SQL Server to Oracle
                odie_63
                From the query I can see it is used on TimingData. so please let me know on how to use.
                Yes, I can see that too...

                What I don't understand though is what sense does it make to order by TimingData.
                I was hoping you could tell me more about the actual business rule that supposed to be implemented here.

                From what I can gather, this :
                ORDER BY Timings.value('(/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData)[1]', 'varchar(100)')
                seems to order by all text nodes (concatenated) under the TimingData element.

                So, ported to Oracle, you may do it like this :
                ROW_NUMBER() OVER(ORDER BY x2.service_name, x2.start_time, x2.end_time)
                Can you confirm that's what you want?
                • 5. Re: SQL Query for reading XML script: Conversion from SQL Server to Oracle
                  860250
                  I am very sorry, I am not sure whats the use of Timing Data. I am new to SQL server & I havent worked on XML's.

                  Please let me know if I can use like below.

                  ROW_NUMBER() OVER(ORDER BY x2.TimingData)
                  • 6. Re: SQL Query for reading XML script: Conversion from SQL Server to Oracle
                    AlexAnd
                    some experiments

                    for sqlserver2008r2
                    DECLARE @MQResponse2 XML
                    SET @MQResponse2 = 
                      '<AD>
                         <InsuranceSvcRs>
                              <RqUID>847C9B80-953A-4867-8304-5A2E0F05E10E</RqUID>
                              <SPName>QMATIO</SPName>
                              <PersAutoPolicyQuoteInqRs>
                                   <RqUID>EC086D43-756B-4F45-A7F1-D9EF99B9CAC8</RqUID>
                                   <TransactionResponseDt>2011-12-16</TransactionResponseDt>
                                   <com.tt_DerivedItems>
                                        <AgentCd>0RV664</AgentCd>
                                        <WritingCompanyCd>THM</WritingCompanyCd>
                                        <MarketCd>A</MarketCd>
                                        <NumericStateCd>02</NumericStateCd>
                                        <EffectiveDt>2012-01-28</EffectiveDt>
                                        <ExpirationDt>2013-01-28</ExpirationDt>
                                        <FormCd>101</FormCd>
                                        <PolicySequenceNumber>1</PolicySequenceNumber>
                                        <PolicyTerm>12</PolicyTerm>
                                        <TotalPremiumAmt>1329.00</TotalPremiumAmt>
                                        <RatingPlan>9</RatingPlan>
                                        <ReportingOffice>183</ReportingOffice>
                                        <AlphaStateCd>AZ</AlphaStateCd>
                                        <ProcessDt>2012-01-28 12:00:00.000</ProcessDt>
                                        <PricingTier>028</PricingTier>
                                        <TotalPremiumAndTaxAmt>1329.00</TotalPremiumAndTaxAmt>
                                        <ProgramCd>QU</ProgramCd>
                                        <OriginalQuoteDt>2011-12-15</OriginalQuoteDt>
                                        <CustomerNumber>0000039176213</CustomerNumber>
                                        <PricingTrack>TR028</PricingTrack>
                                        <PrrInsLpseCd>N</PrrInsLpseCd>
                                        <ServiceOffice>476</ServiceOffice>
                                        <PremiumInfo>
                                             <PremiumCd>PIF</PremiumCd>
                                             <PremiumAmt>1329.00</PremiumAmt>
                                        </PremiumInfo>
                                        <PremiumInfo>
                                             <PremiumCd>INS</PremiumCd>
                                             <PremiumAmt>1400.00</PremiumAmt>
                                        </PremiumInfo>
                                        <TimingData>
                                             <ServiceName>XDX10021-BLD-FDS:SFGA0001</ServiceName>
                                             <StartTime>2011121616045588-0500</StartTime>
                                             <EndTime>2011121616045600-0500</EndTime>
                                        </TimingData>
                                        <TimingData>
                                             <ServiceName>XDX10042-TRN-FDS:SFGA0001</ServiceName>
                                             <StartTime>2011121616045600-0500</StartTime>
                                             <EndTime>2011121616045678-0500</EndTime>
                                        </TimingData>
                                        <TimingData>
                                             <ServiceName>IGA1071V-ADD-GW-CUST:</ServiceName>
                                             <StartTime>2011121616045638-0500</StartTime>
                                             <EndTime>2011121616045644-0500</EndTime>
                                        </TimingData>
                                        <TimingData>
                                             <ServiceName>IOE1023V-SUMMARY-REQUEST</ServiceName>
                                             <StartTime>2011121616045658-0500</StartTime>
                                             <EndTime>2011121616045659-0500</EndTime>
                                        </TimingData>
                                        <TimingData>
                                             <ServiceName>IOE1022V-CRDT-REQ-CK</ServiceName>
                                             <StartTime>2011121616045659-0500</StartTime>
                                             <EndTime>2011121616045662-0500</EndTime>
                                        </TimingData>
                                        <TimingData>
                                             <ServiceName>IOE1002V-REPORT-VIEW-S</ServiceName>
                                             <StartTime>2011121616045662-0500</StartTime>
                                             <EndTime>2011121616045662-0500</EndTime>
                                        </TimingData>
                                        <TimingData>
                                             <ServiceName>XDX10092-MAP-FDS:SFGA0001</ServiceName>
                                             <StartTime>2011121616045678-0500</StartTime>
                                             <EndTime>2011121616045682-0500</EndTime>
                                        </TimingData>
                                        <TimingData>
                                             <ServiceName>M3172F00-CALLCARS:PRICE</ServiceName>
                                             <StartTime>2011121616045685-0500</StartTime>
                                             <EndTime>2011121616045812-0500</EndTime>
                                        </TimingData>
                                        <TimingData>
                                             <ServiceName>M3172F00-CALLCARS:QUOTE</ServiceName>
                                             <StartTime>2011121616045814-0500</StartTime>
                                             <EndTime>2011121616045892-0500</EndTime>
                                        </TimingData>
                                        <TimingData>
                                             <ServiceName>XDX10021-BLD-FDS:SFGA0002</ServiceName>
                                             <StartTime>2011121616045893-0500</StartTime>
                                             <EndTime>2011121616045914-0500</EndTime>
                                        </TimingData>
                                        <TimingData>
                                             <ServiceName>XDX10042-TRN-FDS:SFGA0002</ServiceName>
                                             <StartTime>2011121616045914-0500</StartTime>
                                        </TimingData>
                                        <PLWQ_QueWriteTmstp>2011-12-16-16.04.58.934241</PLWQ_QueWriteTmstp>
                                        <PolicyProcessingEvent>POST RATING</PolicyProcessingEvent>
                                        <ThirdPartyReports>
                                             <CategoryCode>CREDIT</CategoryCode>
                                             <InstanceId>324876227541053</InstanceId>
                                        </ThirdPartyReports>
                                        <AlphaInsuranceScore>AANH</AlphaInsuranceScore>
                                        <InsuranceScoreSourceCd>T</InsuranceScoreSourceCd>
                                   </com.tt_DerivedItems>
                                   <PersPolicy>
                                        <LOBCd>AUTOP</LOBCd>
                                        <ContractTerm>
                                             <EffectiveDt>2012-01-28</EffectiveDt>
                                             <DurationPeriod>
                                                  <NumUnits>12</NumUnits>
                                             </DurationPeriod>
                                        </ContractTerm>
                                        <CreditScoreInfo>
                                             <CSSourceCd>T</CSSourceCd>
                                        </CreditScoreInfo>
                                        <QuoteInfo>
                                             <CompanysQuoteNumber>1387853026</CompanysQuoteNumber>
                                             <com.tt_CustomerNumber>0000039176213</com.tt_CustomerNumber>
                                             <com.tt_CICSRegion>CICTPLH2</com.tt_CICSRegion>
                                             <com.tt_IFSTokenId>223207027541053</com.tt_IFSTokenId>
                                             <com.tt_IFSVendorCd>DMS</com.tt_IFSVendorCd>
                                        </QuoteInfo>
                                   </PersPolicy>
                                   <PersAutoLineBusiness>
                                        <LOBCd>AUTOP</LOBCd>
                                        <Coverage>
                                             <CoverageDesc>Safe Driver Advantage</CoverageDesc>
                                             <CreditOrSurcharge id="CS291762915061053">
                                                  <CreditSurchargeCd>com.tt_SafeDriverAdvantage</CreditSurchargeCd>
                                             </CreditOrSurcharge>
                                        </Coverage>
                                        <Coverage>
                                             <CoverageDesc>Early Quote Advantage</CoverageDesc>
                                             <CreditOrSurcharge id="CS514762915061053">
                                                  <CreditSurchargeCd>com.tt_EarlyQuoteAdvantage</CreditSurchargeCd>
                                             </CreditOrSurcharge>
                                        </Coverage>
                                        <PersVeh id="VEH1">
                                             <Manufacturer>NISSA</Manufacturer>
                                             <Model>SENTRA E/X</Model>
                                             <ModelYear>1994</ModelYear>
                                             <VehIdentificationNumber>1N4EB31F6RC817270</VehIdentificationNumber>
                                             <Coverage>
                                                  <CoverageCd>PD</CoverageCd>
                                                  <CurrentTermAmt>
                                                       <Amt>283.00</Amt>
                                                  </CurrentTermAmt>
                                             </Coverage>
                                             <Coverage>
                                                  <CoverageCd>BI</CoverageCd>
                                                  <CurrentTermAmt>
                                                       <Amt>469.00</Amt>
                                                  </CurrentTermAmt>
                                             </Coverage>
                                             <Coverage>
                                                  <CoverageCd>MEDPM</CoverageCd>
                                                  <CurrentTermAmt>
                                                       <Amt>126.00</Amt>
                                                  </CurrentTermAmt>
                                             </Coverage>
                                             <Coverage>
                                                  <CoverageCd>UM</CoverageCd>
                                                  <CurrentTermAmt>
                                                       <Amt>91.00</Amt>
                                                  </CurrentTermAmt>
                                             </Coverage>
                                             <Coverage>
                                                  <CoverageCd>UNDUM</CoverageCd>
                                                  <CurrentTermAmt>
                                                       <Amt>40.00</Amt>
                                                  </CurrentTermAmt>
                                             </Coverage>
                                             <Coverage>
                                                  <CoverageCd>COMP</CoverageCd>
                                                  <CurrentTermAmt>
                                                       <Amt>185.00</Amt>
                                                  </CurrentTermAmt>
                                             </Coverage>
                                             <Coverage>
                                                  <CoverageCd>COLL</CoverageCd>
                                                  <CurrentTermAmt>
                                                       <Amt>135.00</Amt>
                                                  </CurrentTermAmt>
                                             </Coverage>
                                        </PersVeh>
                                   </PersAutoLineBusiness>
                                   <PolicySummaryInfo>
                                        <PolicyStatusCd>QUOTEDNOTBOUND</PolicyStatusCd>
                                        <FullTermAmt>
                                             <Amt>1329.00</Amt>
                                        </FullTermAmt>
                                        <com.tt_AlternatePremium>1400.00</com.tt_AlternatePremium>
                                        <com.tt_PrimaryPremiumCd>PIF</com.tt_PrimaryPremiumCd>
                                        <com.tt_AlternatePremiumCd>FULL</com.tt_AlternatePremiumCd>
                                   </PolicySummaryInfo>
                                   <MsgStatus>
                                        <MsgStatusCd>SUCCESS</MsgStatusCd>
                                   </MsgStatus>
                                   <RemarkText id="MSG001" IdRef="MSG001">
                                        <ProbLog>NO TEXT FOUND FOR MESSAGE CODE</ProbLog>
                                        <ProbCode>000000306</ProbCode>
                                   </RemarkText>
                              </PersAutoPolicyQuoteInqRs>
                         </InsuranceSvcRs>
                    </AD>'
                    
                    SELECT DerivedItems.Timings.value('(ServiceName)[1]','varchar(100)') col1,
                            DerivedItems.Timings.value('(StartTime)[1]','varchar(100)') col2,
                            DerivedItems.Timings.value('(EndTime)[1]','varchar(100)') col3,
                           Timings.value('(/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData)[1]', 'varchar(100)') col4,
                           ROW_NUMBER() OVER(ORDER BY Timings.value('(/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData)[1]', 'varchar(100)')) col5
                    FROM @MQResponse2.nodes('/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData') as DerivedItems(Timings)
                    with result as
                    col1     col2     col3     col4     col5
                    XDX10021-BLD-FDS:SFGA0001     2011121616045588-0500     2011121616045600-0500     XDX10021-BLD-FDS:SFGA00012011121616045588-05002011121616045600-0500     1
                    XDX10042-TRN-FDS:SFGA0001     2011121616045600-0500     2011121616045678-0500     XDX10021-BLD-FDS:SFGA00012011121616045588-05002011121616045600-0500     2
                    IGA1071V-ADD-GW-CUST:     2011121616045638-0500     2011121616045644-0500     XDX10021-BLD-FDS:SFGA00012011121616045588-05002011121616045600-0500     3
                    IOE1023V-SUMMARY-REQUEST     2011121616045658-0500     2011121616045659-0500     XDX10021-BLD-FDS:SFGA00012011121616045588-05002011121616045600-0500     4
                    IOE1022V-CRDT-REQ-CK     2011121616045659-0500     2011121616045662-0500     XDX10021-BLD-FDS:SFGA00012011121616045588-05002011121616045600-0500     5
                    IOE1002V-REPORT-VIEW-S     2011121616045662-0500     2011121616045662-0500     XDX10021-BLD-FDS:SFGA00012011121616045588-05002011121616045600-0500     6
                    XDX10092-MAP-FDS:SFGA0001     2011121616045678-0500     2011121616045682-0500     XDX10021-BLD-FDS:SFGA00012011121616045588-05002011121616045600-0500     7
                    M3172F00-CALLCARS:PRICE     2011121616045685-0500     2011121616045812-0500     XDX10021-BLD-FDS:SFGA00012011121616045588-05002011121616045600-0500     8
                    M3172F00-CALLCARS:QUOTE     2011121616045814-0500     2011121616045892-0500     XDX10021-BLD-FDS:SFGA00012011121616045588-05002011121616045600-0500     9
                    XDX10021-BLD-FDS:SFGA0002     2011121616045893-0500     2011121616045914-0500     XDX10021-BLD-FDS:SFGA00012011121616045588-05002011121616045600-0500     10
                    XDX10042-TRN-FDS:SFGA0002     2011121616045914-0500     NULL     XDX10021-BLD-FDS:SFGA00012011121616045588-05002011121616045600-0500     11
                    sorry for formatting :(


                    so
                    part
                    SELECT DerivedItems.Timings.value('(ServiceName)[1]','varchar(100)')
                    FROM @MQResponse2.nodes('/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData') as DerivedItems(Timings)
                    transform to oracle side as
                    select service_name
                      from xmltable('/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData'
                                    passing <your_xml> 
                                    columns service_name varchar2(100) path 'ServiceName'
                                   ) x
                    next look at col4 of result of sql query in sqlserver2008r2

                    as we can see it's first occurrence and it's concatenation of several elements
                    so
                    select service_name || start_time || end_time as orderby
                      from xmltable(
                             '/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData'
                             passing <your_xml> 
                             columns service_name varchar2(100) path 'ServiceName'
                                   , start_time   varchar2(100) path 'StartTime'
                                   , end_time     varchar2(100) path 'EndTime'
                                   , id for ordinality
                           ) x2
                     where id = 1 
                    so result of transformation sqlserver2008r2 query to oracle 11.2 (in my case)
                    select service_name, row_number() over(order by orderby)
                      from (select x.*, x3.*
                              from t,
                                   xmltable('/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData'
                                            passing t.xml columns service_name varchar2(100) path
                                            'ServiceName',
                                            start_time varchar2(100) path 'StartTime',
                                            end_time varchar2(100) path 'EndTime'
                                           ) x,
                                   (select service_name || start_time || end_time as orderby
                                      from t,
                                           xmltable('/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData'
                                                    passing t.xml columns service_name
                                                    varchar2(100) path 'ServiceName',
                                                    start_time varchar2(100) path 'StartTime',
                                                    end_time varchar2(100) path 'EndTime',
                                                    id for ordinality) x2
                                     where id = 1) x3
                          )
                    and result of oracle query is
                    Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
                    Connected as apps
                     
                    SQL> 
                    SQL> with t as
                      2  (select xmltype(
                      3  '<AD>
                      4    <InsuranceSvcRs>
                      5      <RqUID>847C9B80-953A-4867-8304-5A2E0F05E10E</RqUID>
                      6      <SPName>QMATIO</SPName>
                      7      <PersAutoPolicyQuoteInqRs>
                      8        <RqUID>EC086D43-756B-4F45-A7F1-D9EF99B9CAC8</RqUID>
                      9        <TransactionResponseDt>2011-12-16</TransactionResponseDt>
                     10        <com.tt_DerivedItems>
                     11          <PremiumInfo>
                     12            <PremiumCd>INS</PremiumCd>
                     13            <PremiumAmt>1400.00</PremiumAmt>
                     14          </PremiumInfo>
                     15          <TimingData>
                     16            <ServiceName>XDX10021-BLD-FDS:SFGA0001</ServiceName>
                     17            <StartTime>2011121616045588-0500</StartTime>
                     18            <EndTime>2011121616045600-0500</EndTime>
                     19          </TimingData>
                     20          <TimingData>
                     21            <ServiceName>XDX10042-TRN-FDS:SFGA0001</ServiceName>
                     22            <StartTime>2011121616045600-0500</StartTime>
                     23            <EndTime>2011121616045678-0500</EndTime>
                     24          </TimingData>
                     25          <TimingData>
                     26            <ServiceName>IGA1071V-ADD-GW-CUST:</ServiceName>
                     27            <StartTime>2011121616045638-0500</StartTime>
                     28            <EndTime>2011121616045644-0500</EndTime>
                     29          </TimingData>
                     30          <TimingData>
                     31            <ServiceName>IOE1023V-SUMMARY-REQUEST</ServiceName>
                     32            <StartTime>2011121616045658-0500</StartTime>
                     33            <EndTime>2011121616045659-0500</EndTime>
                     34          </TimingData>
                     35          <TimingData>
                     36            <ServiceName>IOE1022V-CRDT-REQ-CK</ServiceName>
                     37            <StartTime>2011121616045659-0500</StartTime>
                     38            <EndTime>2011121616045662-0500</EndTime>
                     39          </TimingData>
                     40          <TimingData>
                     41            <ServiceName>IOE1002V-REPORT-VIEW-S</ServiceName>
                     42            <StartTime>2011121616045662-0500</StartTime>
                     43            <EndTime>2011121616045662-0500</EndTime>
                     44          </TimingData>
                     45          <TimingData>
                     46            <ServiceName>XDX10092-MAP-FDS:SFGA0001</ServiceName>
                     47            <StartTime>2011121616045678-0500</StartTime>
                     48            <EndTime>2011121616045682-0500</EndTime>
                     49          </TimingData>
                     50          <TimingData>
                     51            <ServiceName>M3172F00-CALLCARS:PRICE</ServiceName>
                     52            <StartTime>2011121616045685-0500</StartTime>
                     53            <EndTime>2011121616045812-0500</EndTime>
                     54          </TimingData>
                     55          <TimingData>
                     56            <ServiceName>M3172F00-CALLCARS:QUOTE</ServiceName>
                     57            <StartTime>2011121616045814-0500</StartTime>
                     58            <EndTime>2011121616045892-0500</EndTime>
                     59          </TimingData>
                     60          <TimingData>
                     61            <ServiceName>XDX10021-BLD-FDS:SFGA0002</ServiceName>
                     62            <StartTime>2011121616045893-0500</StartTime>
                     63            <EndTime>2011121616045914-0500</EndTime>
                     64          </TimingData>
                     65          <TimingData>
                     66            <ServiceName>XDX10042-TRN-FDS:SFGA0002</ServiceName>
                     67            <StartTime>2011121616045914-0500</StartTime>
                     68          </TimingData>
                     69          <PLWQ_QueWriteTmstp>2011-12-16-16.04.58.934241</PLWQ_QueWriteTmstp>
                     70          <PolicyProcessingEvent>POST RATING</PolicyProcessingEvent>
                     71          <ThirdPartyReports>
                     72            <CategoryCode>CREDIT</CategoryCode>
                     73            <InstanceId>324876227541053</InstanceId>
                     74          </ThirdPartyReports>
                     75          <AlphaInsuranceScore>AANH</AlphaInsuranceScore>
                     76          <InsuranceScoreSourceCd>T</InsuranceScoreSourceCd>
                     77        </com.tt_DerivedItems>
                     78      </PersAutoPolicyQuoteInqRs>
                     79       </InsuranceSvcRs>
                     80  </AD>') xml from dual)
                     81  --
                     82  select service_name, row_number() over(order by orderby)
                     83    from (select x.*, x3.*
                     84            from t,
                     85                 xmltable('/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData'
                     86                          passing t.xml columns service_name varchar2(100) path
                     87                          'ServiceName',
                     88                          start_time varchar2(100) path 'StartTime',
                     89                          end_time varchar2(100) path 'EndTime'
                     90                         ) x,
                     91                 (select service_name || start_time || end_time as orderby
                     92                    from t,
                     93                         xmltable('/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData'
                     94                                  passing t.xml columns service_name
                     95                                  varchar2(100) path 'ServiceName',
                     96                                  start_time varchar2(100) path 'StartTime',
                     97                                  end_time varchar2(100) path 'EndTime',
                     98                                  id for ordinality) x2
                     99                   where id = 1) x3
                    100        )
                    101  /
                     
                    SERVICE_NAME                                                                     ROW_NUMBER()OVER(ORDERBYORDERB
                    -------------------------------------------------------------------------------- ------------------------------
                    XDX10021-BLD-FDS:SFGA0001                                                                                     1
                    XDX10042-TRN-FDS:SFGA0001                                                                                     2
                    IGA1071V-ADD-GW-CUST:                                                                                         3
                    IOE1023V-SUMMARY-REQUEST                                                                                      4
                    IOE1022V-CRDT-REQ-CK                                                                                          5
                    IOE1002V-REPORT-VIEW-S                                                                                        6
                    XDX10092-MAP-FDS:SFGA0001                                                                                     7
                    M3172F00-CALLCARS:PRICE                                                                                       8
                    M3172F00-CALLCARS:QUOTE                                                                                       9
                    XDX10021-BLD-FDS:SFGA0002                                                                                    10
                    XDX10042-TRN-FDS:SFGA0002                                                                                    11
                     
                    11 rows selected
                     
                    SQL> 
                    compare to result of sqlserver2008r2 :)

                    --add
                    it's also may be as
                    select service_name, id
                          from t,
                               xmltable('/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData'
                                        passing t.xml columns service_name varchar2(100) path
                                        'ServiceName',
                                        start_time varchar2(100) path 'StartTime',
                                        end_time varchar2(100) path 'EndTime',
                                        id for ordinality) x
                    -- add 2

                    very strange that col4 of query in sqlserver2008r2 the same
                    may be in other version it's different for different rows
                    and
                    ROW_NUMBER() OVER(ORDER BY Timings.value('(/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData)[1]', 'varchar(100)')),
                    will be
                    ROW_NUMBER() OVER(ORDER BY service_name||start_time||end_time)
                    and
                    select service_name, ROW_NUMBER() OVER(ORDER BY service_name||start_time||end_time)
                          from t,
                               xmltable('/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData'
                                        passing t.xml columns service_name varchar2(100) path
                                        'ServiceName',
                                        start_time varchar2(100) path 'StartTime',
                                        end_time varchar2(100) path 'EndTime'
                                       ) x
                    Edited by: AlexAnd on Aug 29, 2012 11:06 AM
                    • 7. Re: SQL Query for reading XML script: Conversion from SQL Server to Oracle
                      odie_63
                      Alex,

                      I think your last query (which is pretty much like the one I suggested) makes more sense than the first one.

                      The first one, using the subquery X3 and the ORDERBY column, is just ordering by a constant string, which actually means no ordering at all.


                      @OP :
                      I am very sorry, I am not sure whats the use of Timing Data. I am new to SQL server & I havent worked on XML's.
                      Well, you'd better know what's the use of it if you intend to make a correct migration.
                      Some constructs are really close to Oracle syntax hence easily ported, others need the knowledge of business rules.
                      • 8. Re: SQL Query for reading XML script: Conversion from SQL Server to Oracle
                        AlexAnd
                        >
                        I think your last query (which is pretty much like the one I suggested) makes more sense than the first one.
                        >
                        yes yes

                        >
                        The first one, using the subquery X3 and the ORDERBY column, is just ordering by a constant string, which actually means no ordering at all.
                        >
                        of course

                        but this transformation one to one and step by step :)
                        of course in oracle side it's may be tuning (may be like last query)



                        2OP:
                        you must examine main sql server query and plz post for us sqlserver version and sample output of sql server query

                        if
                        Timings.value('(/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData)[1]', 'varchar(100)')
                        is constant for all rows
                        then in oracle side
                        ROWNUM
                        or in xmltable clause
                        , id for ordinality
                        if different and like to concatenation of several rows then
                        ROW_NUMBER() OVER(ORDER BY service_name||start_time||end_time)