tsql - T-SQL: query xml with namespace -
i've done basic xml work in t-sql before, literally 'basic'.
now come across more complex xml , flummoxed begin.
<?xml version="1.0" encoding="utf-8"?> <calculation:scenario xmlns:calculation="http://www.sap.com/ndb/bimodelcalculation.ecore" schemaversion="2.3" id="adr2" applyprivilegetype="analytic_privilege" checkanalyticprivileges="true" defaultclient="$$client$$" defaultlanguage="$$language$$" visibility="internal" calculationscenariotype="tree_based" datacategory="dimension" enforcesqlexecution="false" executionsemantic="undefined" outputviewtype="projection"> <origin/> <descriptions defaultdescription="adr2"/> <metadata activatedat="2015-04-22 16:13:29.0" changedat="2015-04-22 21:12:59.193"/> <localvariables/> <variablemappings/> <datasources> <datasource id="adr2" .... all attempts bring <blank> guessing issue semicolon "calculation:scenario" , "xmlns:calculation" googling far, 'namespace'.
however, in examples have found of querying xml namespaces, source xml has property such : xmlns:ns="uri"
they use in query : ";with xmlnamespaces ('uri' ns)"
my xml not have ns attribute.
could give me pointers begin, or basic tutorial includes scenario ?
many thanks
one example select element or attribute in namespace :
declare @xml xml = '<?xml version="1.0" encoding="utf-8"?> <calculation:scenario xmlns:calculation="http://www.sap.com/ndb/bimodelcalculation.ecore" schemaversion="2.3" id="adr2" applyprivilegetype="analytic_privilege" checkanalyticprivileges="true" defaultclient="$$client$$" defaultlanguage="$$language$$" visibility="internal" calculationscenariotype="tree_based" datacategory="dimension" enforcesqlexecution="false" executionsemantic="undefined" outputviewtype="projection"> <origin/> <descriptions defaultdescription="adr2"/> <metadata activatedat="2015-04-22 16:13:29.0" changedat="2015-04-22 21:12:59.193"/> <localvariables/> <variablemappings/> </calculation:scenario>' select @xml.value('declare namespace calc="http://www.sap.com/ndb/bimodelcalculation.ecore"; (calc:scenario/@id)[1]', 'varchar(max)') 'scenario_id' output :
basically need declare mapping of namespace prefix (calc) namespace uri (http://www.sap.com/ndb/bimodelcalculation.ecore), use declared prefix in xquery statement ((calc:scenario/@id)[1]). mentioned steps demonstrated in above example.
for reference :
Comments
Post a Comment