T-SQL：使用命名空间查询xml

I've done some basic xml work in T-SQL before, but literally 'basic'.

我以前在T-SQL中完成了一些基本的xml工作,但实际上是“基本的”。

Now come across some more complex xml and I am completely flummoxed as to where to begin.

现在遇到一些更复杂的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/>
<dataSources>
    <DataSource id="ADR2" ....

All my attempts just bring back <blank> I am guessing the issue is the semicolon "Calculation:scenario" and "xmlns:Calculation" From googling so far, this is a 'namespace'.

我的所有尝试都带回来 我猜这个问题是分号“计算:场景”和“xmlns:计算”从google搜索到目前为止,这是一个'命名空间'。

However, in all examples I have found of querying xml with namespaces, the source xml has a property such as : xmlns:ns="uri"

但是,在我发现的使用命名空间查询xml的所有示例中,源xml具有如下属性:xmlns:ns =“uri”

They then use this in the query : ";WITH XMLNAMESPACES ('uri' as ns)"

然后他们在查询中使用它:“; WITH XMLNAMESPACES('uri'as ns)”

My xml does not have this ns attribute.

我的xml没有此ns属性。

Could anyone give me any pointers as to where to begin, or some basic tutorial that includes my scenario ?

任何人都可以给我任何关于从哪里开始的指示,或者包括我的场景的一些基本教程?

Many Thanks

1 个解决方案

#1

One example to select an 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)') as 'scenario_id'

Output :

Basically you need to declare mapping of namespace prefix (calc) to namespace URI (http://www.sap.com/ndb/BiModelCalculation.ecore), then use the declared prefix properly in the XQuery statement ((calc:scenario/@id)[1]). All mentioned steps are demonstrated in the above example.

基本上你需要声明名称空间前缀(calc)到名称空间URI(http://www.sap.com/ndb/BiModelCalculation.ecore)的映射,然后在XQuery语句中正确使用声明的前缀((calc:scenario / @) id)的[1])。在上面的例子中演示了所有提到的步骤。

For reference :

以供参考 :

• MSDN: XML.value()

• MSDN: XML.nodes()