Querying XML Data stored in SQL Server -


i have column called resume of type xml stored in table in ms-sql server.

i want retrieve candidates city saginaw.

when use query 

select resume.query('(: explicit namespace :)declare namespace ns="namespace-resume";                 //ns:address/ns:addr.location/ns:location/ns:loc.city') jobcandidate

i values city fine when use following query:

select resume.query('(: explicit namespace :)declare namespace ns="namespace-resume";                 //ns:location[@ns:loc.city="saginaw"]/ns:name') jobcandidate

i error "there not attribute named loc.city".

the sample data below:

<ns:resume xmlns:ns="namespace-resume"> <ns:name>     <ns:name.prefix></ns:name.prefix>     <ns:name.first>shai</ns:name.first>     <ns:name.middle></ns:name.middle>     <ns:name.last>bassli</ns:name.last>     <ns:name.suffix></ns:name.suffix>   </ns:name> <ns:address> <ns:addr.type>home</ns:addr.type> <ns:addr.street>567 3rd ave</ns:addr.street> <ns:addr.location>   <ns:location>     <ns:loc.countryregion>us </ns:loc.countryregion>     <ns:loc.state>mi </ns:loc.state>     <ns:loc.city>saginaw</ns:loc.city>   </ns:location> </ns:addr.location> </ns:address> </ns:resume>

your question not clear... i'll provide several approaches, 1 of them point way (you can copy whole lot query window , execute stand-alone):

declare @xml xml= n'<ns:resume xmlns:ns="namespace-resume">   <ns:name>     <ns:name.prefix />     <ns:name.first>shai</ns:name.first>     <ns:name.middle />     <ns:name.last>bassli</ns:name.last>     <ns:name.suffix />   </ns:name>   <ns:address>     <ns:addr.type>home</ns:addr.type>     <ns:addr.street>567 3rd ave</ns:addr.street>     <ns:addr.location>       <ns:location>         <ns:loc.countryregion>us </ns:loc.countryregion>         <ns:loc.state>mi </ns:loc.state>         <ns:loc.city>saginaw</ns:loc.city>       </ns:location>     </ns:addr.location>   </ns:address> </ns:resume>';

--read 1 element's text namespaces wildcards

select @xml.value(n'(/*:resume/*:name/*:name.first/text())[1]',n'nvarchar(max)');

--use default namespace

with xmlnamespaces(default n'namespace-resume') select @xml.value(n'(/resume/address/addr.location/location/loc.city/text())[1]',n'nvarchar(max)');

--your sample xml includes 1 person only, assume there more --use predicate name given location

declare @location nvarchar(100)=n'saginaw';--change tests xmlnamespaces(default n'namespace-resume') select @xml.value(n'(/resume[(address/addr.location/location/loc.city/text())[1]=sql:variable("@location")]/name/name.first/text())[1]',n'nvarchar(max)');

--read several values of node --use predicate name given location

with xmlnamespaces(default n'namespace-resume') select r.value(n'(name/name.first/text())[1]',n'nvarchar(max)')       ,r.value(n'(name/name.last/text())[1]',n'nvarchar(max)')  @xml.nodes(n'/resume[(address/addr.location/location/loc.city/text())[1]=sql:variable("@location")]') a(r);

update: select provide in comment

the call nodes() missing? try this:

declare @location nvarchar(100)=n'saginaw';  xmlnamespaces(default n'namespace-resume')  select r.value(n'(name/name.first/text())[1]',n'nvarchar(max)')       ,r.value(n'(name/name.last/text())[1]',n'nvarchar(max)')  jobcandidate cross apply resume.nodes(n'/resume[(address/addr.location/location/loc.city/text())[1]=sql:variable("@location")]') a(r);

update 2: namespace according comment

declare @xml xml= n'<ns:resume xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/resume">   <ns:name>     <ns:name.prefix />     <ns:name.first>shai</ns:name.first>     <ns:name.middle />     <ns:name.last>bassli</ns:name.last>     <ns:name.suffix />   </ns:name>   <ns:address>     <ns:addr.type>home</ns:addr.type>     <ns:addr.street>567 3rd ave</ns:addr.street>     <ns:addr.location>       <ns:location>         <ns:loc.countryregion>us </ns:loc.countryregion>         <ns:loc.state>mi </ns:loc.state>         <ns:loc.city>saginaw</ns:loc.city>       </ns:location>     </ns:addr.location>   </ns:address> </ns:resume>'; declare @location nvarchar(100)=n'saginaw';--change tests  xmlnamespaces(default n'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/resume') select r.value(n'(name/name.first/text())[1]',n'nvarchar(max)')       ,r.value(n'(name/name.last/text())[1]',n'nvarchar(max)')  @xml.nodes(n'/resume[(address/addr.location/location/loc.city/text())[1]=sql:variable("@location")]') a(r);

Comments

Post a Comment

Popular posts from this blog

Command prompt result in label. Python 2.7 -

i want put result(output) of shell command label. command works, label shows "0", however, in command prompt, result shown correctly, need showed in label. doing ubuntu. myg1 = button(root, text="rodyti informacija", command=lambda: gauti()) myg1.pack(side=bottom) def gauti(): imti = tekstas.get("1.0", "end-1c") info = subprocess.call("id '{imti}' ".format(imti=imti), shell=true) w = label(root, text= info) w.pack(side = bottom) subprocess.call() returns exit code of process created (in case 0 success). if want text output of process, should instead call subprocess.check_output() .
Read more

javascript - How do I use URL parameters to change link href on page? -

i change href value of link using url parameters. example: default <a id="dlink" href="link-1">link</a> but, when user goes https://url.com/?dlink=link-2 it swaps out link new link <a id="dlink" href="link-2">link</a> first need value url. if you're not using javascript framework can create function using jquery. please check get url parameter jquery or how query string values in js it. after that, element "a" id , set value there. $("#dlink").attr("href", hreffromparam);
Read more

amazon web services - AWS Route53 Trying To Get Site To Resolve To www -

so have domain nameservers pointed aws route 53. site hosted on ec2 instance , reachable resolves site.com, if input www.site.com. i'm curious how site resolve www.site.com. have under route53 domains hosted zone 2 additional record sets outside of ns , soa: a site.com ec2 elastic ip cname www.site.com ec2 public dns i can reach site using both site.com , www.site.com, resolve site.com. there way make if enter either, resolves www.site.com? at first, both should record set. a site.com ec2 elastic ip www.site.com ec2 elastic ip and need redirect setting in web server (nginx, apache, ..) following. (source: how redirect www non-www nginx on centos 7 ) redirect non-www www if want redirect users plain, non-www domain www domain, add server block: server { server_name example.com; return 301 $scheme://www.example.com$request_uri; } save , exit. configures nginx redirect requests "example.com" "www.example.com". not...
Read more