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