CODEWITHSUNDEEP
xml
user3262640
user3262640

Reputation: 27

SQL Query XML Issue

I am having issues obtaining the value from the CultureString tag within the olapDatasource tag in the xml, specifically the value attribute in this example I should be returning 'CubeTwo' but to no avail.

I have tried select

xml.value('declare namespace x="urn:Schemas-Zap-BI-Resource"; declare namespace f="urn:Schemas-Zap-BI-LocalizableString"; (/x:SearchMetadata/Connection/SerializableServerConnectionInfo/OlapServerConnectionInfo/OlapDataSource/LocalizableString/CultureStrings/CultureString/@Value)[0]', 'nvarchar(max)')

from Resource

But I get NULL. Any Ideas how to return this value ?

<SearchMetadata xmlns="urn:Schemas-Zap-BI-Resource">
  <Properties Id="fc124f36-45f4-4847-b7b6-01754e10bdc8" UniqueId="c270485a-3106-4dec-be77-127bfbf98f24" ResourceType="Analysis" Version="6.4.39306.17532" SystemResource="false" SolutionName="Solution.ZapTechnologyDevelopment.AXDiscreteManufacturing" Licensed="false" PolicyRoot="false">
    <DatabaseRowVersion>"AAAAAAABGVA="</DatabaseRowVersion>
    <Name DefaultCulture="en-US" DefaultCultureValue="Quick Ratio Trend">
      <CultureStrings xmlns="urn:Schemas-Zap-BI-LocalizableString">
        <CultureString Culture="en-US" Value="Quick Ratio Trend" LowerValue="quick ratio trend" />
      </CultureStrings>
    </Name>
    <Created>2014-08-01T13:13:15.1449044+01:00</Created>
    <Modified>2014-08-01T12:37:36.9173669Z</Modified>
    <ModifiedBy Name="Schuster" UserName="ADMINIS-CMNLQS3\Schuster" Email="">
    </ModifiedBy>
    <Parent Id="6fc3df4f-0241-4db1-8b49-c8bea68e90cb" />
    <Policy Id="c00daeaa-0a98-4a02-b746-3253bcc47e51" />
    <PendingImportId xmlns:p3="http://www.w3.org/2001/XMLSchema-instance" p3:nil="true" />
    <ExtendedProperties TypeName="Zap.BI.Resources.CellSets.CellSetProperties, Zap.BI.Resources">
      <CellSetProperties>
        <DisplayChart>true</DisplayChart>
        <DisplayPivotTable>false</DisplayPivotTable>
      </CellSetProperties>
    </ExtendedProperties>
  </Properties>
  <ResourceReferences>
  </ResourceReferences>
  <Connection xmlns="">
    <SerializableServerConnectionInfo TypeName="Zap.BI.Resources.DataSources.OlapServerConnectionInfo, Zap.BI.Resources">
      <OlapServerConnectionInfo>
        <OlapDataSource ResourceTypeName="OlapDataSource" ReferenceType="Persisted" Id="{14131aa1-8223-40bd-ae7d-f035f9f09374}">
          <LocalizableString xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" DefaultCulture="en-US" DefaultCultureValue="CubeTwo">
            <CultureStrings xmlns="urn:Schemas-Zap-BI-LocalizableString">
              <CultureString Culture="en-US" Value="CubeTwo" LowerValue="cubetwo" />
            </CultureStrings>
          </LocalizableString>
          <DisconnectedResourceOfOlapDataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
        </OlapDataSource>
      </OlapServerConnectionInfo>
    </SerializableServerConnectionInfo>
    <DatabaseName>Dynamics AX 2012R2 An</DatabaseName>
    <CubeName>Dynamics AX</CubeName>
    <ConfiguredCubeReference ResourceTypeName="ConfiguredCube" ReferenceType="Persisted" Id="{df13a7c0-2845-48b0-8a7e-ec65ffd57e4c}">
      <LocalizableString xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" DefaultCulture="en-US" DefaultCultureValue="Dynamics AX 2012">
        <CultureStrings xmlns="urn:Schemas-Zap-BI-LocalizableString">
          <CultureString Culture="en-US" Value="Dynamics AX 2012" LowerValue="dynamics ax 2012" />
        </CultureStrings>
      </LocalizableString>
      <DisconnectedResourceOfConfiguredCube xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
    </ConfiguredCubeReference>
    <PerspectiveId>00000000-0000-0000-0000-000000000000</PerspectiveId>
  </Connection>
</SearchMetadata>

Upvotes: 0

Views: 65

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

You were close. If you look closely at your attempt, you'll see that you've declared but not used one of your namespaces. You need to use it twice. Also, the index should be 1, not 0:

select @x.value('declare namespace x="urn:Schemas-Zap-BI-Resource";
declare namespace f="urn:Schemas-Zap-BI-LocalizableString";
(/x:SearchMetadata/Connection/SerializableServerConnectionInfo/OlapServerConnectionInfo/
OlapDataSource/LocalizableString/f:CultureStrings/f:CultureString/@Value)[1]',
'nvarchar(max)')

Result:

CubeTwo

Upvotes: 0

Related Questions