Referencing namespace

Question

Hi, I'm sure this is a simple newbie quesiton, but I've been googling for a while now.

Where I have schema data like this:

<xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="AuditData">
  <xs:element name="AuditData" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="EntityBand">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Signonname" msdata:Caption="Sign on name" type="xs:string" minOccurs="0" />
              <xs:element name="Description" type="xs:string" minOccurs="0" />
              <xs:element name="Emailaddress" msdata:Caption="Email address" type="xs:string" minOccurs="0" />
              <xs:element name="Department" type="xs:string" minOccurs="0" />
              <xs:element name="Position" type="xs:string" minOccurs="0" />
              <xs:element name="Securitylevel" msdata:Caption="Security level" type="xs:string" minOccurs="0" />
              <xs:element name="Disallowlogon" msdata:Caption="Disallow log on" type="xs:boolean" minOccurs="0" />
              <xs:element name="Requireusertochangepassword" msdata:Caption="Require user to change password" type="xs:boolean" minOccurs="0" />
              <xs:element name="GroupMembership" msdata:Caption="Group Membership" type="xs:string" minOccurs="0" />
              <xs:element name="Password" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>

Which is stored in an XML column in a SQL table, how do I get to the msdata:Caption value?

I have a query like this to get to the name and type values:

SELECT ea.ID, 
       d.value('(@name)[1]', 'NVARCHAR(255)')             AS 'Name',
       d.value('(@type)[1]', 'NVARCHAR(255)')             AS 'Type'
FROM   EXTENDED_AUDIT ea
CROSS APPLY ea.AuditDataSchema.nodes('/*/*/*/*/*/*/*/*') y(d)

Many thanks

Answer 1

E.g.

DECLARE @Data XML = N'
<xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="AuditData">
  <xs:element name="AuditData" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="EntityBand">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Signonname" msdata:Caption="Sign on name" type="xs:string" minOccurs="0" />
              <xs:element name="Description" type="xs:string" minOccurs="0" />
              <xs:element name="Emailaddress" msdata:Caption="Email address" type="xs:string" minOccurs="0" />
              <xs:element name="Department" type="xs:string" minOccurs="0" />
              <xs:element name="Position" type="xs:string" minOccurs="0" />
              <xs:element name="Securitylevel" msdata:Caption="Security level" type="xs:string" minOccurs="0" />
              <xs:element name="Disallowlogon" msdata:Caption="Disallow log on" type="xs:boolean" minOccurs="0" />
              <xs:element name="Requireusertochangepassword" msdata:Caption="Require user to change password" type="xs:boolean" minOccurs="0" />
              <xs:element name="GroupMembership" msdata:Caption="Group Membership" type="xs:string" minOccurs="0" />
              <xs:element name="Password" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>
';

WITH	XMLNAMESPACES ( 'http://www.w3.org/2001/XMLSchema' AS xs, 'urn:schemas-microsoft-com:xml-msdata' AS msdata )
SELECT	d.value('@name[1]', 'NVARCHAR(255)') AS [Name] ,
		d.value('@type[1]', 'NVARCHAR(255)') AS [Type] ,
		d.value('@msdata:Caption[1]', 'NVARCHAR(255)') AS Caption
FROM	@Data.nodes('//xs:element[@msdata:Caption]') y(d);

Answer 2

Try this:

;WITH XMLNAMESPACES ( 'urn:schemas-microsoft-com:xml-msdata' AS msdata )
SELECT ea.ID, 
       d.value('(@name)[1]', 'NVARCHAR(255)')             AS 'Name',
       d.value('(@type)[1]', 'NVARCHAR(255)')             AS 'Type',
       d.value('(@msdata:Caption)[1]', 'NVARCHAR(255)')	  AS 'Caption'
FROM   EXTENDED_AUDIT ea
CROSS APPLY ea.AuditDataSchema.nodes('//*[@msdata:Caption]') y(d)

Answer 3

Try this:

;WITH XMLNAMESPACES ( 'urn:schemas-microsoft-com:xml-msdata' AS msdata )
SELECT ea.ID, 
       d.value('(@name)[1]', 'NVARCHAR(255)')             AS 'Name',
       d.value('(@type)[1]', 'NVARCHAR(255)')             AS 'Type',
       d.value('(@msdata:Caption)[1]', 'NVARCHAR(255)')	  AS 'Caption'
FROM   EXTENDED_AUDIT ea
CROSS APPLY ea.AuditDataSchema.nodes('//*[@msdata:Caption]') y(d)

Perfect. Many thanks.

PS Apologies for the delay in responding - must remember when I'm about to go on holiday in future!