parse more than one different values from xml to sql -
i have situation have this:
create table _golink_xml ( recordid int identity(1,1) not null primary key, xmldata xml not null ) go insert _golink_xml(xmldata) select * openrowset( bulk 'c:\golink_0607.xml', single_blob) importsource go xml this
<main> <export> <date>2015-07-06</date> <time>11:57:39</time> <fromdate>2015-07-06</fromdate> <todate>2015-07-06</todate> </export> <tickets> <ticket no="831-9275972480" status="issued" type="electron" signinbooking="3osws" signinticketing="72" reissueno=""> <ticketheader> <history ticketing="2015-07-06" void="" refund="" reissue="" /> <created>2015-07-06 11:29:00</created> <modified /> <modifiedby /> <clientcompanyname /> <clientnumber /> <distrubutorname /> <distributornumber /> <recordlocator>rk3hdw</recordlocator> <subagent /> <interline>n</interline> <ticketstatus>ok</ticketstatus> <tktcoupons /> <globalcouponstatus /> <termid>bdb7f9</termid> <mirdev>fed02b</mirdev> <transbeforetkt>016</transbeforetkt> <autoprice>n</autoprice> <bookeddirectly>n</bookeddirectly> <frequentflyer /> <farecalc>zag ou dbv 92.00ou zag 92.00 hrk184.00end xt 125.00zs110.00yq</farecalc> <routing>zag-dbv-zag</routing> <itineraryindicator>d</itineraryindicator> <pnrcreationdate>2015-07-03</pnrcreationdate> <officeid booking="3os " tr="" ticketing="3os " /> <contacts>zagn*385915885134__zagt*00385 1 6329-100 aviokarte.hr-ip__zage*aviokarte//aviokarte.hr</contacts> <tktagenttitle /> <tktofficetitle>3os</tktofficetitle> <bookingofficetitle>3os</bookingofficetitle> <iata>75321164</iata> <validairlinename>croatia airlines</validairlinename> <validatingcarrier>ou</validatingcarrier> <rate currency="hrk" value="184.00" /> <ratehrk>0.00</ratehrk> <convertationrate>0.0000</convertationrate> <tourcode /> <comission>0.00a</comission> <commisamt curr="hrk" value="0.00" /> <fp>invoice</fp> <creditcard /> <airrecloc>1a-8fdqtv</airrecloc> <endorsement>ou only/ /refund not permitted/ </endorsement> <amount>635.00</amount> <servicefee main="0.00" vat="0.00" /> <discount>0.00</discount> <clienttopay>0.00</clienttopay> <airlinepaytotal>635,00</airlinepaytotal> <netearnedexcltaxes>0,00</netearnedexcltaxes> <agentcountry>hr </agentcountry> <remarks> <remark /> <remark1 /> <remark2>di.fa2: di.fa3: igor di.fa4: jurisic di.fa5: ogrizoviceva 34 di.fa6: 10000 di.fa7: zagreb di.fa8: hr di.fa9: bt di.fa10: 03072015 di.fa13: 385915885134 di.fa20: di.fa21: hrk 368 902 40 di.fa30: uhr </remark2> <remark3 /> </remarks> <taxes curr="hrk" value="451.00"> <tax curr="hrk" value="206.00" tc="hr" nc="" /> <tax curr="hrk" value="10.00" tc="mi" nc="" /> <tax curr="hrk" value="110.00" tc="yq" nc="" /> <tax curr="hrk" value="125.00" tc="zs" nc="" /> </taxes> <carriertktfees /> </ticketheader> <ne no="1" lastname="jurisic" firstname="igormr" title="" text="adt" /> <airsegments> <airsegment no="01" class="u"> <servicingcarrier>ou</servicingcarrier> <servicingcarriername>croatia airlines</servicingcarriername> <opratingcarriername /> <flightno>660</flightno> <seatnumber /> <flightdurationtime>0055</flightdurationtime> <geographicalmileage>00245</geographicalmileage> <meal>n</meal> <mealssr /> <airclass>u</airclass> <departure date="2015-10-02" time="05:50:00" /> <arrival date="2015-10-02" time="06:45:00" /> <board city="zagreb" point="zag" terminal="" /> <off city="dubrovnik" point="dbv" terminal="" /> <farebasis>uprom</farebasis> <baggageallowance>1pc</baggageallowance> <equipment>319</equipment> <wchr /> <acrecloc /> </airsegment> <airsegment no="02" class="u"> <servicingcarrier>ou</servicingcarrier> <servicingcarriername>croatia airlines</servicingcarriername> <opratingcarriername /> <flightno>669</flightno> <seatnumber /> <flightdurationtime>0055</flightdurationtime> <geographicalmileage>00245</geographicalmileage> <meal>n</meal> <mealssr /> <airclass>u</airclass> <departure date="2015-10-06" time="21:20:00" /> <arrival date="2015-10-06" time="22:15:00" /> <board city="dubrovnik" point="dbv" terminal="" /> <off city="zagreb" point="zag" terminal="" /> <farebasis>uprom</farebasis> <baggageallowance>1pc</baggageallowance> <equipment>319</equipment> <wchr /> <acrecloc /> </airsegment> </airsegments> </ticket> </tickets> </main> my code goes this:
select pref.value('(date/text())[1]', 'date') date, pref.value('(time/text())[1]', 'time') time, pref.value('(fromdate/text())[1]', 'date') fromdate, pref.value('(todate/text())[1]', 'date') todate, pref.value('(status/text())[1]','varchar(50)') status _golink_xml cross apply xmldata.nodes('main/export') a(pref) i this:
date time fromdate todate status 2015-07-06 11:57:39.000000 2015-07-06 2015-07-06 null if use:
select pref.value('(date/text())[1]', 'date') date, pref.value('(time/text())[1]', 'time') time, pref.value('(fromdate/text())[1]', 'date') fromdate, pref.value('(todate/text())[1]', 'date') todate, pref.value('(status/text())[1]','varchar(50)') status _golink_xml cross apply xmldata.nodes('main/export') a(pref) cross apply xmldata.nodes('tickets/ticket') a(pref) i got nothing.
does know doing wrong?
do proper results code?
select exportdate = colexport.value('(date)[1]', 'date') , exporttime = colexport.value('(time)[1]', 'time'), exportfromdate = colexport.value('(fromdate)[1]', 'date'), exporttodate = colexport.value('(todate)[1]', 'date'), -- use @status xml attribute (not element!) "status" ticketstatus = colticket.value('@status','varchar(50)') _go_link_xml cross apply -- nodes <main>/<export> have base data xmldata.nodes('main/export') xt1(colexport) cross apply -- nodes <main>/<tickets>/<ticket> have ticket-related data xmldata.nodes('main/tickets/ticket') xt2(colticket) i data i'm expecting - if there 2 <ticket> nodes in xml (which assume you're trying achieve - correct?)
your problems were:
you didn't have right xpath
<tickets>- missed fact under<main>, toowhen have 2 separate
.nodes(), should not use same aliases xml pseudo table , columns! (as a(pref)) use unique aliases each case
Comments
Post a Comment