sql server - T-SQL XPath query to join an Attribute from an ancestor to a child element -


i have following xml document fragments:

<document id="856" version="004010" direction="outbound">   <xpaths>     <xpath id="deliveraddresscode">//ib//transaction[1]/loop[@loopid="n1"]/n1[n101="st"][n103="zz"]/n104/text()</xpath>   </xpaths>   <schema>     <bsn bsn01="00" bsn02="{xpath::concat(substring(asn[1]/asnh[1]/senderid[1],1,5),asn[1]/asnh[1]/dino[1])}" bsn03="{xpath::.//asnh/date/text()}" bsn04="{xpath::.//asnh/time/text()}" />     <dtm dtm01="011" dtm02="{xpath::.//asnh/shipdate/text()}" />     <hl hl01="1" hl02="" hl03="s">       <td1 td101="plt" td102="{xpath::.//asnh/loadingqty/text()}" td103="" td104="" td105="" td106="n" td107="{xpath::.//asnh/zweight/text()}" td108="kg" />       <td5 td501="" td502="" td503="" td504="m" td505="{sql::select [dbo].[fslocaltoremotemapping]('{xpath::.//asnh/baanid/text()}','{xpath::.//asnh/isausageindicator}','forwardingagents','{xpath::.//asnh/cfrw/text()}')}" />       <td3 td301="tl" td302="" td303="{xpath::.//asnh/trackingno/text()}" />       <per per01="ic" per02="?" per03="em" per04="?@?.com" />       <n1 n101="sf" n102="{xpath:://sf/nama/text()}" n103="zz" n104="{xpath:://sf/cuno/text()}">         <n3_sf n301="{xpath:://sf/namc/text()}" />         <n4_sf n401="{xpath:://sf/cityname/text()}" n402="{xpath:://sf/prov/text()}" n403="{xpath:://sf/pstc/text()}" n404="{xpath:://sf/country/text()}" />       </n1>       <n1 n101="st" n102="{xpath:://ib//transaction[1]/loop[@loopid='n1']/n1[n101='st'][n103='zz']/n102/text()}" n103="zz" n104="{xpath:://ib//transaction[1]/loop[@loopid='n1']/n1[n101='st'][n103='zz']/n104/text()}" />     </hl>   </schema>   <scripts>     <function id="fcdec">   function fcdec(x) { if(x  &gt;= 3500.00) return "002" ; else return "002"; }</function>   </scripts> </document>

and (fragment of above):

<schema>   <bsn bsn01="00" bsn02="{xpath::concat(substring(asn[1]/asnh[1]/senderid[1],1,5),asn[1]/asnh[1]/dino[1])}" bsn03="{xpath::.//asnh/date/text()}" bsn04="{xpath::.//asnh/time/text()}" />   <dtm dtm01="011" dtm02="{xpath::.//asnh/shipdate/text()}" />   <hl hl01="1" hl02="" hl03="s">     <td1 td101="plt" td102="{xpath::.//asnh/loadingqty/text()}" td103="" td104="" td105="" td106="n" td107="{xpath::.//asnh/zweight/text()}" td108="kg" />     <td5 td501="" td502="" td503="" td504="m" td505="{sql::select [dbo].[fslocaltoremotemapping]('{xpath::.//asnh/baanid/text()}','{xpath::.//asnh/isausageindicator}','forwardingagents','{xpath::.//asnh/cfrw/text()}')}" />     <td3 td301="tl" td302="" td303="{xpath::.//asnh/trackingno/text()}" />     <per per01="ic" per02="? edi services" per03="em" per04="?@?.com" />     <n1 n101="sf" n102="{xpath:://sf/nama/text()}" n103="zz" n104="{xpath:://sf/cuno/text()}">       <n3_sf n301="{xpath:://sf/namc/text()}" />       <n4_sf n401="{xpath:://sf/cityname/text()}" n402="{xpath:://sf/prov/text()}" n403="{xpath:://sf/pstc/text()}" n404="{xpath:://sf/country/text()}" />     </n1>     <n1 n101="st" n102="{xpath:://ib//transaction[1]/loop[@loopid='n1']/n1[n101='st'][n103='zz']/n102/text()}" n103="zz" n104="{xpath:://ib//transaction[1]/loop[@loopid='n1']/n1[n101='st'][n103='zz']/n104/text()}" />   </hl> </schema>

how retrieve document id first fragment second 1 query result is:

<schema id="856">   <bsn bsn01="00" bsn02="{xpath::concat(substring(asn[1]/asnh[1]/senderid[1],1,5),asn[1]/asnh[1]/dino[1])}" bsn03="{xpath::.//asnh/date/text()}" bsn04="{xpath::.//asnh/time/text()}" />   <dtm dtm01="011" dtm02="{xpath::.//asnh/shipdate/text()}" />   <hl hl01="1" hl02="" hl03="s">     <td1 td101="plt" td102="{xpath::.//asnh/loadingqty/text()}" td103="" td104="" td105="" td106="n" td107="{xpath::.//asnh/zweight/text()}" td108="kg" />     <td5 td501="" td502="" td503="" td504="m" td505="{sql::select [dbo].[fslocaltoremotemapping]('{xpath::.//asnh/baanid/text()}','{xpath::.//asnh/isausageindicator}','forwardingagents','{xpath::.//asnh/cfrw/text()}')}" />     <td3 td301="tl" td302="" td303="{xpath::.//asnh/trackingno/text()}" />     <per per01="ic" per02="? edi services" per03="em" per04="?@?.com" />     <n1 n101="sf" n102="{xpath:://sf/nama/text()}" n103="zz" n104="{xpath:://sf/cuno/text()}">       <n3_sf n301="{xpath:://sf/namc/text()}" />       <n4_sf n401="{xpath:://sf/cityname/text()}" n402="{xpath:://sf/prov/text()}" n403="{xpath:://sf/pstc/text()}" n404="{xpath:://sf/country/text()}" />     </n1>     <n1 n101="st" n102="{xpath:://ib//transaction[1]/loop[@loopid='n1']/n1[n101='st'][n103='zz']/n102/text()}" n103="zz" n104="{xpath:://ib//transaction[1]/loop[@loopid='n1']/n1[n101='st'][n103='zz']/n104/text()}" />   </hl> </schema>

the query used 

    --alter function [dbo].[fxdocschema](@env varchar(10),@baanid varchar(10),@docid varchar(10)) returns xml     begin         declare @baanid varchar(10)='010-000164',@env varchar(10)='test',@docid varchar(10)='856'     --return(      select t.c.query('.')                  ,t.c.query('schema')     (select meta edimeta baanid=@baanid , env=case lower(@env)                                                                                                                                                 when 't' 'test'                                                                                                                                                  when 'p' 'production'                                                                                                                                                  else @env                                                                                                                                      end) f(x)     cross applyx.nodes('meta/partners/partner/documents/document[@id=sql:variable("@docid")]') t(c)      --)  end

something this?

declare @xml xml = n'<document id="856" version="004010" direction="outbound">   <xpaths>     <xpath id="deliveraddresscode">//ib//transaction[1]/loop[@loopid="n1"]/n1[n101="st"][n103="zz"]/n104/text()</xpath>   </xpaths>   <schema>     <bsn bsn01="00" bsn02="{xpath::concat(substring(asn[1]/asnh[1]/senderid[1],1,5),asn[1]/asnh[1]/dino[1])}" bsn03="{xpath::.//asnh/date/text()}" bsn04="{xpath::.//asnh/time/text()}" />     <dtm dtm01="011" dtm02="{xpath::.//asnh/shipdate/text()}" />     <hl hl01="1" hl02="" hl03="s">       <td1 td101="plt" td102="{xpath::.//asnh/loadingqty/text()}" td103="" td104="" td105="" td106="n" td107="{xpath::.//asnh/zweight/text()}" td108="kg" />       <td5 td501="" td502="" td503="" td504="m" td505="{sql::select [dbo].[fslocaltoremotemapping](''{xpath::.//asnh/baanid/text()}'',''{xpath::.//asnh/isausageindicator}'',''forwardingagents'',''{xpath::.//asnh/cfrw/text()}'')}" />       <td3 td301="tl" td302="" td303="{xpath::.//asnh/trackingno/text()}" />       <per per01="ic" per02="?" per03="em" per04="?@?.com" />       <n1 n101="sf" n102="{xpath:://sf/nama/text()}" n103="zz" n104="{xpath:://sf/cuno/text()}">         <n3_sf n301="{xpath:://sf/namc/text()}" />         <n4_sf n401="{xpath:://sf/cityname/text()}" n402="{xpath:://sf/prov/text()}" n403="{xpath:://sf/pstc/text()}" n404="{xpath:://sf/country/text()}" />       </n1>       <n1 n101="st" n102="{xpath:://ib//transaction[1]/loop[@loopid=''n1'']/n1[n101=''st''][n103=''zz'']/n102/text()}" n103="zz" n104="{xpath:://ib//transaction[1]/loop[@loopid=''n1'']/n1[n101=''st''][n103=''zz'']/n104/text()}" />     </hl>   </schema>   <scripts>     <function id="fcdec">   function fcdec(x) { if(x  &gt;= 3500.00) return "002" ; else return "002"; }</function>   </scripts> </document>'  select      x.value('@id', 'int') "@id" ,   (     select          y.query('.') "*"     @xml.nodes('document/schema/*') a(y)     xml path(''), type     ) @xml.nodes('document') a(x) xml path('schema')

five principles:

  1. i isolate main level of want @ root main query(the outer a(x) root of 'document')
  2. i isolate children(a(y)) , query on children of document/schema/*. if ran inner see children nodes on 3 rows.
  3. i don't care them use query of parts , label them text "*".
  4. i specify type of xml in nested select don't want give level on it, keep itself. (for xml path(''), type)
  5. i set attribute main id again , xml expression path ignore root setting (for xml path('schema'))

Comments

Post a Comment

Popular posts from this blog

ubuntu - PHP script to find files of certain extensions in a directory, returns populated array when run in browser, but empty array when run from terminal -

i running os ububtu 16.04. ultimate goal able run php script cron job every minute. here php script located @ /var/www/html/tests/test/index.php : <?php $directorypath = $_server['document_root']."/tests/test/data/"; echo "directorypath: $directorypath<br><br>";//check $imagefilepathsarray = glob($directorypath . "*.{png,gif}", glob_brace); echo "<br><br>imagefilepathsarray: "; print_r($imagefilepathsarray);//check ?> when open php script in browser visiting url my-ip-address/tests/test/index.php , get: directorypath: /var/www/html/tests/test/data/ imagefilepathsarray:array( [0] => /var/www/html/tests/test/data/pic.png [1] => /var/www/html/tests/test/data/pic.gif ) before writing cron job in crontab file, need check if can run anywhere using terminal . opened ubuntu terminal @ /home/ location, , executed in terminal: sudo php /var/www/html/tests/test/index.php i got following out...
Read more

php - How can i create a user dashboard -

today have questions you, want create user dashboard site, include (obviously), users data, example -email -username -and others stuff how can information, , print them in html page? if can, how can these information via php sessions? shuld use session? example, if user login, , start new session ( session_start() ), how can data print in dashboard? should use php "project"? in advice, have day , thank time!
Read more

javascript - How to detect toggling of the fullscreen-toolbar in jQuery Mobile? -

i have jquery mobile page fixed full-screen toolbar, data-tap-toggle enabled. straight under toolbar have positioned banner should slide upwards when toolbar hiding , slide downward when toolbar showing. jquery mobile toggles toolbar applying , removing ui-fixed-hidden class - sadly, can't find in documentation of toolbar widget toggle , hide or show event that. how can detect when toolbar toggled, reposition banner? .banner { position: fixed; background-color: darkseagreen; top: 46px; min-height: 48px; width: 100%; text-align: center; line-height: 48px; } <!doctype html> <html> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"> <script src="https://code.jquery.com/jquery-1....
Read more