Parse XML value with Name Space in SQl -


i have following xml :

<productionschedule xmlns:inp2="http://www.wbf.org/xml/b2mml-v0401" xmlns="http://www.wbf.org/xml/b2mml-v0401">   <inp2:productionrequest>     <inp2:id>0916a</inp2:id>     <inp2:description>subh190916a</inp2:description>     <inp2:location>       <inp2:equipmentid>myequpiment</inp2:equipmentid>     </inp2:location>     <inp2:segmentrequirement>       <inp2:id>000</inp2:id>       <inp2:earlieststarttime>2015-10-17t12:00:00</inp2:earlieststarttime>       <inp2:latestendtime>2015-10-19t12:00:00</inp2:latestendtime>       <inp2:materialproducedrequirement>         <inp2:materialdefinitionid>geec3ma0025emzi</inp2:materialdefinitionid>         <inp2:quantity>           <inp2:quantitystring>2</inp2:quantitystring>         </inp2:quantity>         <inp2:materialproducedrequirementproperty>           <inp2:id>erpwostatus</inp2:id>           <inp2:value>             <inp2:valuestring>released</inp2:valuestring>           </inp2:value>         </inp2:materialproducedrequirementproperty>         <inp2:materialproducedrequirementproperty>           <inp2:id>routing</inp2:id>           <inp2:value>             <inp2:valuestring>somemprvaue</inp2:valuestring>           </inp2:value>         </inp2:materialproducedrequirementproperty>         <inp2:materialproducedrequirementproperty>           <inp2:id>mpvalue2</inp2:id>           <inp2:value>             <inp2:valuestring>2016-01-21t12:00:00</inp2:valuestring>           </inp2:value>         </inp2:materialproducedrequirementproperty>       </inp2:materialproducedrequirement>     </inp2:segmentrequirement>   </inp2:productionrequest> </productionschedule>

i trying value mpvalue2 , xml.

i tried following:

select `@xml.value('(/productionschedule/inp2:productionrequest/inp2:segmentrequirement/inp2:materialproducedrequirement/inp2:materialproducedrequirementproperty)[1]','nvarchar(255)')`

your select ok, must consider/declare namespaces:

with xmlnamespaces(default 'http://www.wbf.org/xml/b2mml-v0401'                   ,'http://www.wbf.org/xml/b2mml-v0401' inp2) select @xml.value('(/productionschedule/inp2:productionrequest/inp2:segmentrequirement/inp2:materialproducedrequirement/inp2:materialproducedrequirementproperty)[1]','nvarchar(255)')

this works (wildcard) it's better specific possible:

select @xml.value('(/*:productionschedule/*:productionrequest/*:segmentrequirement/*:materialproducedrequirement/*:materialproducedrequirementproperty)[1]','nvarchar(255)')

the fast , lazy work :-) not fast in terms of performance...

select @xml.value('(//*:materialproducedrequirementproperty)[1]','nvarchar(255)')

update

this query properties:

with xmlnamespaces(default 'http://www.wbf.org/xml/b2mml-v0401'                   ,'http://www.wbf.org/xml/b2mml-v0401' inp2) select prop.value('(inp2:id)[1]','nvarchar(100)') property @xml.nodes('/productionschedule/inp2:productionrequest/inp2:segmentrequirement/inp2:materialproducedrequirement/inp2:materialproducedrequirementproperty') a(prop)

the result

property -------- erpwostatus routing mpvalue2

update 2: use id filter in xquery

see how added filter @ end of xpath in .nodes().

nodes return sub-elements row-wise. filter reduce resultset 1 single row (if inp2:id unique!) , read value/valuestring.

i let namespace declaration default , inp2. but, @serf pointed out correctly, both urls equal. enough declare default , query without namespace-prefixes...

declare @theid nvarchar(100)='mpvalue2'; xmlnamespaces(default 'http://www.wbf.org/xml/b2mml-v0401'                   ,'http://www.wbf.org/xml/b2mml-v0401' inp2) select prop.value('(inp2:value/inp2:valuestring)[1]','nvarchar(100)') property @xml.nodes('/productionschedule/inp2:productionrequest/inp2:segmentrequirement/inp2:materialproducedrequirement/inp2:materialproducedrequirementproperty[inp2:id=sql:variable("@theid")]') a(prop)

-

Comments

Post a Comment

Popular posts from this blog

angular - Is it possible to get native element for formControl? -

i've got angular2 reactive form . created formcontrol s , assigned input fields by [formcontrol]=... . understand creates nativeelement <-> formcontrol link. my question: possible nativeelement formcontrol ? wanna myformcontrol.nativeelement.focus() i can share 1 terrible solution works me. in reactive forms can use either 1) formcontroldirective ts mycontrol = new formcontrol('') template <input type="text" [formcontrol]="mycontrol"> or 2) formcontrolname ts myform: formgroup; constructor(private fb: formbuilder) {} ngoninit() { this.myform = this.fb.group({ foo: '' }); } template <form [formgroup]="myform"> <input type="text" formcontrolname="foo"> </form> so these directives write patch like 1) formcontroldirective const originformcontrolngonchanges = formcontroldirective.prototype.ngonchanges; formcontroldirective.proto
Read more

unity3d - Rotate an object to face an opposite direction -

i have object , b. when click on object b, rotates object b (a faces b). b doesn't face a. need following : when faces b, need face opposite direction. have code rotating @ b. how rotate face opposite direction after that? vector3 targetdirection = target - transform.position; float step = speed * time.deltatime; vector3 newdirection = vector3.rotatetowards (turretdome.transform.forward, targetdirection, step, 0.0f); turretdome.transform.rotation = quaternion.lookrotation (newdirection); you object facing object b, want inverse direction of object after that? objecta.transform.rotation = quaternion.inverse(objecta.transform.rotation) but lets assume example turretdome object a, (negate direction): turretdome.transform.rotation = quaternion.lookrotation (-newdirection); naturally, both of these snippets not show how smoothen rotation, seem know how use time.deltatime. just incase unsure, quaternion.lerp this
Read more

javascript - Why jQuery Select box change event is now working? -

i working on product filters user select select box according his/her preference not getting selected value user in jquery. html code:- <select name="product_filter" id="product_filter"> <option value="price_low_first">price : low high</option> <option value="price_high_first">price : high low</option> <option value="latest">latest</option> <option value="popular">most popular</option> </select> jquery code: $(function () { $("#product_filter").change(function () { alert("hi") var selectedtext = $(this).find("option:selected").text(); var selectedvalue = $(this).val(); alert("selected text: " + selectedtext + " value: " + selectedvalue); }); }); hi believe code perfect. once please check jquery.min.js
Read more