SQL Server XSD SimpleType xs:list as table?

Question

SQL server has support for XML, but I cannot figure out how to get it to work with the list type

<?xml version="1.0" encoding="utf-16"?>
<xsd:schema id="XMLSchema1"
   targetNamespace="http://tempuri.org/XMLSchema1.xsd"
   elementFormDefault="qualified"
   xmlns="http://tempuri.org/XMLSchema1.xsd"
   xmlns:mstns="http://tempuri.org/XMLSchema1.xsd"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
  <xsd:simpleType name="valuelist">
     <xsd:list itemType="xsd:integer"/>
  </xsd:simpleType>
  <xsd:element name="a" type="valuelist"/>

I cannot figure out how to make this work:

DECLARE @p0 AS XML
SET @p0 = '<a>123 124</a>'
select ??? from @p0.???

This one works fine, but it has an overhead of 6 chars extra per number:

DECLARE @p0 AS XML
SET @p0 = '<b>123</b><b>124</b>'
select T.c.value('.', 'int') as Id from @p0.nodes('/b') AS T(c)

Answer 1

Here's a fully working example:

DECLARE @Schema XML
SET @Schema = '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"&gt;
<xs:element name="a">
    <xs:simpleType>
        <xs:list itemType="xs:int" />
    </xs:simpleType>
</xs:element>
</xs:schema>'

CREATE XML SCHEMA COLLECTION exampleschema as @Schema;
GO

DECLARE @XmlDoc AS XML(exampleschema)
SET @XmlDoc = '<a>123 456 789</a>'
select T.ref.value('.', 'int')
from 
(
      select [Xml][email protected]('
            for $i in data(/a) return 
            element temp { $i }
        ')
) A
CROSS APPLY A.Xml.nodes('/temp') T(ref)

DROP XML SCHEMA COLLECTION exampleschema
GO

This is pretty much taken from this MSDN blog that demonstrates the approach.