Read xsi:type from T-SQL

Question

I would like to read the xsi:type attribute from the "current" node in a SELECT statement. My XML looks like this:

 <ns2:data xmlns:ns2="http://mynamespace">
  <OrderLineItems>
    <OrderLineItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns2:DeactivationLineItem" id="1">
      <Product>
        <Id>5300</Id>
        <Description>DUMMY</Description>
        <Domain>ddd</Domain>
      </Product>
      <Quantity>1</Quantity>
    </OrderLineItem>
    <OrderLineItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns2:ActivationLineItem" id="4">
      <Product>
        <Id>5340</Id>
        <Description>DUMMY</Description>
        <Domain>aaa</Domain>
      </Product>
      <Quantity>1</Quantity>
    </OrderLineItem>
    <OrderLineItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns2:DeactivationLineItem" id="12">
      <Product>
        <Id>53200</Id>
        <Description>DUMMY</Description>
        <Domain>ccc</Domain>
      </Product>
      <Quantity>21</Quantity>
    </OrderLineItem>
  </OrderLineItems>
</ns2:data>

My select statement looks as follows:

;WITH XMLNAMESPACES('http://mynamespace' AS ns)
SELECT
,OrderLineItemID                = ref.value('@id', 'int')
,OrderLineItemParentID          = ref.value('@parentId', 'int')
,ProductID                      = NULLIF(ref.query('Product/Id').value('.', 'varchar(255)'),'')
,ProductDescription             = NULLIF(ref.query('Product/Description').value('.', 'varchar(255)'),'')
,ProductDomain                  = NULLIF(ref.query('Product/Domain').value('.', 'varchar(255)'),'')
,ProductAdditionalInfo          = NULLIF(ref.query('Product/AdditionalInfo').value('.', 'varchar(255)'),'')
,Quantity                       = ref.query('Quantity').value('.', 'int')

,LineItemType                   = ref.value('@xsi:type','varchar(max)')                 

FROM tTEMP_XMLTABLE
CROSS APPLY xmlFile.nodes('/ns:data/OrderLineItems/OrderLineItem') R(ref)

My problem is the line LineItemType as it throws an error: The XQuery syntax '@{http://www.w3.org/2001/XMLSchema-instance}:type' is not supported

It is strange, because I am able to read a single type if I don't use the CROSS APPLY:

WITH  XMLNAMESPACES ('http://mynamespace' as p)
SELECT CAST(xmlFile as XML).value('(/p:data/OrderLineItems/OrderLineItem/@xsi:type)[1]','nvarchar(max)')
from tTEMP_XMLTABLE;

The second statement works on SQL Server 2005. Is there a possibility to read the xsi:type attribute when using cross apply?

Thanks for the help

[This article][1] might be of some help... [1]: http://stackoverflow.com/questions/2477237/how-to-select-the-value-of-the-xsitype-attribute-in-sql-server — mwigdahl, Jan 24 '12 at 14:25
Thanks, but this describes the second sql statement i've posted. I need to read ALL Types in a CrossApply — merror, Jan 24 '12 at 14:27
Interesting. What type is "xmlFile"? I was able to get this to work for your xml if I stored it in an xml-typed variable. — mwigdahl, Jan 24 '12 at 14:32
"xmlFile" is a column Name in the tTEMP_XMLTABLE, Datatype is XML (with an XSD schema behind it) — merror, Jan 24 '12 at 14:35
Ah, that may be the issue then. See below for how I was able to get it to work by putting it into an untyped xml field in a table (in this case, a table variable). Not sure if that's possible for you, but it does work. — mwigdahl, Jan 24 '12 at 14:38

mwigdahl · Accepted Answer · 2012-01-24T14:42:49.470

This worked for me in SQL Server 2008 SP1 (you didn't specify version, so I'm not sure if that is what you have or not). Not sure if it's possible for you to get your xml or a copy of your xml into an untyped xml field like I have here, but that might help you get around the schema-bound issue from the link I gave you above.

DECLARE @tmp_xml TABLE (id int identity, data xml)

INSERT INTO @tmp_xml (data)
VALUES ('<ns2:data xmlns:ns2="http://mynamespace">
  <OrderLineItems>
    <OrderLineItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns2:DeactivationLineItem" id="1">
      <Product>
        <Id>5300</Id>
        <Description>DUMMY</Description>
        <Domain>ddd</Domain>
      </Product>
      <Quantity>1</Quantity>
    </OrderLineItem>
    <OrderLineItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns2:ActivationLineItem" id="4">
      <Product>
        <Id>5340</Id>
        <Description>DUMMY</Description>
        <Domain>aaa</Domain>
      </Product>
      <Quantity>1</Quantity>
    </OrderLineItem>
    <OrderLineItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns2:DeactivationLineItem" id="12">
      <Product>
        <Id>53200</Id>
        <Description>DUMMY</Description>
        <Domain>ccc</Domain>
      </Product>
      <Quantity>21</Quantity>
    </OrderLineItem>
  </OrderLineItems>
</ns2:data>')

;WITH XMLNAMESPACES('http://mynamespace' AS ns)
SELECT
OrderLineItemID                = ref.value('@id', 'int')
,OrderLineItemParentID          = ref.value('@parentId', 'int')
,ProductID                      = NULLIF(ref.query('Product/Id').value('.', 'varchar(255)'),'')
,ProductDescription             = NULLIF(ref.query('Product/Description').value('.', 'varchar(255)'),'')
,ProductDomain                  = NULLIF(ref.query('Product/Domain').value('.', 'varchar(255)'),'')
,ProductAdditionalInfo          = NULLIF(ref.query('Product/AdditionalInfo').value('.', 'varchar(255)'),'')
,Quantity                       = ref.query('Quantity').value('.', 'int')

,LineItemType                   = ref.value('@xsi:type','varchar(max)')                 

FROM @tmp_xml t
    CROSS APPLY data.nodes('/ns:data/OrderLineItems/OrderLineItem') R(ref)

thanks a lot, this works! but i cannot use an untyped XML Field. Is there an possibility to use it with an typed XML (XSD Schema behind)? — merror, Jan 24 '12 at 15:06
Based on that other article, I'd say no. But could you perhaps augment the schema of your table with a computed column that converts your typed XML to untyped, and use that for the purposes of type extraction? You could still use the typed XML column for any operations that require the bound schema. — mwigdahl, Jan 24 '12 at 15:42
Thanks a lot. i now use a typed XML for all values and a tempTable with an untyped XML Column to update the typed value. It's not very nice, but it works ;-) — merror, Jan 24 '12 at 16:54
Cool, wish I could have found a better answer for you, but at least you're off the ground with this! — mwigdahl, Jan 24 '12 at 17:27

Read xsi:type from T-SQL

1 Answers1