CODEWITHSUNDEEP
sql-serverxml
Geralt
Geralt

Reputation: 142

Query XML value in sql

I need to get some information from XML in SQL Server 2008, but I cannot even get basic attribute from it. All samples that I tried failed. Table name is Item, xml column name is Data.

Simplified xml looks like this:

<AnchoredXml xmlns="urn:schema:Microsoft.Rtc.Management.ScopeFramework.2008" SchemaWriteVersion="2">
  <Key ScopeClass="Global">
    <SchemaId Namespace="urn:schema:Microsoft.Rtc.Management.Deploy.Topology.2008" ElementName="Topology" />
    <AuthorityId Class="Host" InstanceId="00000000-0000-0000-0000-000000000000" />
  </Key>
  <Dictionary Count="1">
    <Item>
      <Key />
      <Value Signature="a3502dd0-8c16-4023-9eea-30ea1c7a3a2b">
        <Topology xmlns="urn:schema:Microsoft.Rtc.Management.Deploy.Topology.2008">
          <Services>
            <Service RoleVersion="1" ServiceVersion="6" Type="Microsoft.Rtc.Management.Deploy.Internal.ServiceRoles.FileStoreService">
              <ServiceId SiteId="1" RoleName="FileStore" Instance="1" />
              <DependsOn />
              <InstalledOn>
                <ClusterId SiteId="1" Number="1" />
              </InstalledOn>
              <Ports xmlns="urn:schema:Microsoft.Rtc.Management.Deploy.ServiceRoles.2008" />
              <FileStoreService xmlns="urn:schema:Microsoft.Rtc.Management.Deploy.ServiceRoles.2008" ShareName="lyncShare" />
            </Service>
          </Services>
        </Topology>
      </Value>
    </Item>
  </Dictionary>
</AnchoredXml>

I need to read information in AnchoredXml/Key/SchemaId/@NameSpace to select the right xml (there are more rows). Sample xml above is the right one. And after that I need to find the right service with 

Type="Microsoft.Rtc.Management.Deploy.Internal.ServiceRoles.FileStoreService"

where is FileStoreService/@ShareName that I need.

I've tried to print the Namespace attributte for the start, but no sample code is working. A few tries:

SELECT c.p.value('(@Namespace)[1]', 'varchar(50)') as 'Nmspace'
FROM Item
CROSS APPLY Data.nodes('/AnchoredXml/Key/SchemaId') c(p)

returns empty result set

SELECT Data.value('(/AnchoredXml/Key/SchemaId/@Namespace)[1]', 'varchar(50)') 
FROM Item

returns NULL for all rows

SELECT
It.Data.exist('/AnchoredXml/Key/SchemaId[@Namespace="Microsoft.Rtc.Management.Deploy.Topology.2008"]')
FROM [xds].[dbo].[Item] AS It

returns 0's for all rows also without quotes ("")

A working sample code to get at least attribute test would be maybe sufficient and I would figure out the rest. Could you please help me find errors in my queries or maybe identify some other problem? Thanks

Upvotes: 1

Views: 127

Answers (1)

marc_s
marc_s

Reputation: 755321

You're ignoring all the XML namespaces in your XML document! You need to pay attention to those and respect them!

There are XML namespaces on:

  • the root node <AnchoredXml>
    (XML namespace: urn:schema:Microsoft.Rtc.Management.ScopeFramework.2008)
  • the subnode <Topology>
    (XML ns: urn:schema:Microsoft.Rtc.Management.Deploy.Topology.2008)
  • the subnode <FileStoreService>
    (XML ns: urn:schema:Microsoft.Rtc.Management.Deploy.ServiceRoles.2008)

Try this:

-- respect the XML namespaces!!
;WITH XMLNAMESPACES(DEFAULT 'urn:schema:Microsoft.Rtc.Management.ScopeFramework.2008', 
                    'urn:schema:Microsoft.Rtc.Management.Deploy.Topology.2008' AS t,
                    'urn:schema:Microsoft.Rtc.Management.Deploy.ServiceRoles.2008' AS fss)
SELECT
    ShareName = Data.value('(/AnchoredXml/Dictionary/Item/Value/t:Topology/t:Services/t:Service/fss:FileStoreService/@ShareName)[1]', 'varchar(50)')
FROM
    dbo.Item

In my case, this returns:

ShareName
-----------
lyncShare

Upvotes: 1

Related Questions