How to select the value of the xsi:type attribute in SQL Server?

Considering this xml document:

DECLARE @X XML (DOCUMENT search.SearchParameters)  = '<parameters xmlns="http://www.educations.com/Search/Paramete开发者_StackOverflow中文版rs.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <parameter xsi:type="category" categoryID="38" />
</parameters>';

I'd like to access the value of the attribute "type".

According to this blog post, the xsi:type attribute is special and can't be accessed by usual keywords/functions.

How can I do it?

PS: I tried with

WITH XMLNAMESPACES (
 'http://www.educations.com/Search/Parameters.xsd' as p,
 'http://www.w3.org/2001/XMLSchema-instance' as xsi)
  SELECT @X.value('(/p:parameters/p:parameter/@xsi:type)[1]','nvarchar(max)')

but it didn't work.

---

Without specifying collection, this works fine for me:

DECLARE @X XML
SET @x = N' 
<parameters xmlns="http://www.educations.com/Search/Parameters.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <parameter  xsi:type="category" categoryID="38" />
</parameters>'
;

WITH    XMLNAMESPACES
        (
        'http://www.educations.com/Search/Parameters.xsd' as p,
        'http://www.w3.org/2001/XMLSchema-instance' as xsi
        )
SELECT  @X.value('(/p:parameters/p:parameter/@xsi:type)[1]','nvarchar(max)')

Could you please post contents of search.SearchParameters?

Update:

On schema-bound XML, this seems to be impossible.

You can cast your column into a freetype XML:

WITH    XMLNAMESPACES
        (
        'http://www.educations.com/Search/Parameters.xsd' as p,
        'http://www.w3.org/2001/XMLSchema-instance' as xsi
        )
SELECT  CAST(@X AS XML).value('(/p:parameters/p:parameter/@xsi:type)[1]','nvarchar(max)')

(but you won't be able to use XML indexes of any on your column), or perform a boolean check on a specific type:

WITH XMLNAMESPACES
        (
        'http://www.educations.com/Search/Parameters.xsd' as p
        )
SELECT @X.query('(/p:parameters/p:parameter)[1] instance of element(*, p:category?)')

---

I know this is an old question, however I just ran into this issue yesterday and have found no obvious answer to this limitation in SQL. However, if you have control over the schema I have determined a work around.

Simply create an attribute on each subtype with the same name (widgetType in my example below).

Set each attribute to a simple type of xsi:string and put a restriction on it so the only value is the name of your subtype. Additionally, set this as the default value for the attribute.

If you bind this schema to your xml data column, you will always be able to query this attribute that essentially mirrors the xsi:type value.

I admit this is not ideal, but it is better than casting the value as untyped and losing the benefits of your indexes.

Here is an example:

<?xml version="1.0" encoding="utf-8"?>
<xs:schema targetNamespace="http://tempuri.org/XMLSchema.xsd"
           elementFormDefault="qualified"
           xmlns="http://tempuri.org/XMLSchema.xsd"
           xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
           xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="widget"
              type="baseWidget" />
  <xs:complexType name="baseWidget"
                  abstract="true"></xs:complexType>
  <xs:complexType name="widgetA">
    <xs:complexContent>
      <xs:extension base="baseWidget">
        <xs:attribute name="widgetType"
                      default="widgetA">
          <xs:simpleType>
            <xs:restriction base="xs:string">
              <xs:enumeration value="widgetA" />
            </xs:restriction>
          </xs:simpleType>
        </xs:attribute>
      </xs:extension>
    </xs:complexContent>
  </xs:complexType>
  <xs:complexType name="widgetB">
    <xs:complexContent>
      <xs:extension base="baseWidget">
        <xs:attribute name="widgetType"
                      default="widgetB">
          <xs:simpleType>
            <xs:restriction base="xs:string">
              <xs:enumeration value="widgetB" />
            </xs:restriction>
          </xs:simpleType>
        </xs:attribute>
      </xs:extension>
    </xs:complexContent>
  </xs:complexType>
</xs:schema>

If you were to put in an xml entry into a table bound to this schema without the widgetType attribute, SQL would automatically add it due to the default. It will always be available for you to query against.

<?xml version="1.0" encoding="utf-8"?>
<widget xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:type="widgetA"
        widgetType="widgetA"
        xmlns="http://tempuri.org/XMLSchema.xsd" />