SQL Server Grabbing Value from XML parameter to use in later query -

i new sql server , stored procedures begin with. need able parse incoming xml file specific element's value , compare/save later in procedure.

i have few things stacked against me. 1 element need buried inside document. have had no luck in searching name using methods similar this:

    select currentbod = c.value('(local-name(.))[1]', 'varchar(max)'),                 c.value('(.)[1]', 'varchar(max)') @xml.nodes('putmessage/payload/content/acknowledgepartsorder/applicationarea/bodid') bodtable(c) 

it returns null.

so, trying similar this:

    declare @bodtable table(fieldname varchar(max),                     fieldvalue varchar(max))     select     fieldname = nodes.value('local-name(.)', 'varchar(50)'),     fieldvalue = nodes.value('(.)[1]', 'varchar(50)')         @xml.nodes('//*') bodtable(nodes)      declare @currentbod varchar(36)     set @currentbod = ''      set @currentbod = (select fieldvalue @bodtable fieldname = 'bodid') 

this provides me list of node names , values correctly (i test in query , bodtable has elements listed correct values), when set @currentbod comes null.

am missing easier way this? messing these 2 approaches somehow?

here part of xml parsing reference:

<soap:envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"     xmlns:xsi="http://www.w3.org/2001/xmlschema-instance"    xmlns:xsd="http://www.w3.org/2001/xmlschema"           xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/03/addressing"     xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-     secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-     wss-wssecurity-utility-1.0.xsd">       <soap:header> <payloadmanifest xmlns="???">   <c contentid="content0" namespaceuri="???" element="acknowledgepartsorder" version="4.0" /> </payloadmanifest> <wsa:action>http://www.starstandards.org/webservices/2005/10/transport/operations/putmessage</wsa:action> <wsa:messageid>uuid:df8c66af-f364-4b8f-81d8-06150da14428</wsa:messageid> <wsa:replyto>   <wsa:address>http://schemas.xmlsoap.org/ws/2004/03/addressing/role/anonymous</wsa:address> </wsa:replyto> <wsa:to>???</wsa:to> <wsse:security soap:mustunderstand="1">   <wsu:timestamp wsu:id="timestamp-bd91e76f-c212-4555-9b23-f66f839672bd">     <wsu:created>2013-01-03t21:52:48z</wsu:created>     <wsu:expires>2013-01-03t21:53:48z</wsu:expires>   </wsu:timestamp>   <wsse:usernametoken xmlns:wsu="???" wsu:id="???">     <wsse:username>???</wsse:username>     <wsse:password type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#passwordtext">???</wsse:password>     <wsse:nonce>???</wsse:nonce>     <wsu:created>2013-01-03t21:52:48z</wsu:created>   </wsse:usernametoken> </wsse:security>   </soap:header>   <soap:body> <putmessage xmlns="??????">   <payload>     <content id="???">       <acknowledgepartsorder xmlns="???" xmlns:xsi="???" xsi:schemalocation="??? ???" revision="???" release="???" environment="???n" lang="en-us" bodversion="???">         <applicationarea>           <sender>             <component>???</component>             <task>???</task>             <referenceid>???</referenceid>             <creatornamecode>???</creatornamecode>             <sendernamecode>???</sendernamecode>             <dealernumber>???</dealernumber>             <partyid>???</partyid>             <locationid />             <serviceid />           </sender>           <creationdatetime>2013-01-03t21:52:47</creationdatetime>           <bodid>71498800-c098-4885-9ddc-f58aae0e5e1a</bodid>           <destination>             <destinationnamecode>???</destinationnamecode> 

you need respect xml namespaces!

first of all, target xml node <bodid> inside <soap:envelope> , <soap:body> tags - both need included in selection.

secondly, both <putmessage> <acknowledgepartsorder> nodes appear have default xml namespaces (those xmlns=.... without prefix) - , must respected when select data using xpath.

so assuming <putmessage xmlns="urn:pm"> , <acknowledgepartsorder xmlns="urn:apo"> (those guesses on part - replace actual xml namespaces haven't shown use here), should able use xpath you're looking for:

;with xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' soap,                      'urn:pm' ns, 'urn:apo' apo) select     xc.value('(apo:bodid)[1]', 'varchar(100)')      @yourxmlvariable.nodes('/soap:envelope/soap:body/ns:putmessage/ns:payload/ns:content/apo:acknowledgepartsorder/apo:applicationarea') xt(xc) 

this return expected value (71498800-c098-4885-9ddc-f58aae0e5e1a) in case.