SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DECLARE @xml XML = '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:simpleType name="NHSNumberStatusIndicatorCode_Withheld_Type">
    <xs:restriction base="ns:AlphaNumeric_Type">
        <xs:length value="2"/>
        <xs:enumeration value="01"/>
        <xs:enumeration value="02"/>
        <xs:enumeration value="03"/>
        <xs:enumeration value="04"/>
        <xs:enumeration value="05"/>
        <xs:enumeration value="06"/>
        <xs:enumeration value="07"/>
        <xs:enumeration value="08"/>
    </xs:restriction>
  </xs:simpleType>
  <xs:simpleType name="CDSOrganisationIdentifier_Type">
    <xs:restriction base="ns:AlphaNumeric_Type">
        <xs:minLength value="3"/>
        <xs:maxLength value="5"/>
    </xs:restriction>
  </xs:simpleType>
  <xs:simpleType name="Number_Type">
    <xs:restriction base="xs:string">
        <xs:pattern value="[0-9]{10}"/>
    </xs:restriction>
  </xs:simpleType>
</xs:schema >'

SELECT
    S.Node.value('(@name)[1]', 'nvarchar(max)') AS Field_Name,
    R.Node.value('(@base)[1]', 'nvarchar(max)') AS Type,
    R.Node.value('(xs:length/@value)[1]', 'int') AS Length,
    E.Node.value('@value[1]', 'nvarchar(max)') AS enumeration,
    R.Node.value('(xs:maxLength/@value)[1]', 'int') AS MaxLength,
    R.Node.value('(xs:minLength/@value)[1]', 'int') AS MinLength,
    R.Node.value('(xs:pattern/@value)[1]', 'nvarchar(max)') AS Pattern
FROM @xml.nodes('//xs:simpleType') S(Node)
OUTER APPLY S.Node.nodes('xs:restriction') R(Node) -- Likely always one occurrence
OUTER APPLY R.Node.nodes('xs:enumeration') E(Node) -- Zero or more occurrences

Field_Name	Type	Length	enumeration	MaxLength	MinLength	Pattern
NHSNumberStatusIndicatorCode_Withheld_Type	ns:AlphaNumeric_Type	2	01	null	null	null
NHSNumberStatusIndicatorCode_Withheld_Type	ns:AlphaNumeric_Type	2	02	null	null	null
NHSNumberStatusIndicatorCode_Withheld_Type	ns:AlphaNumeric_Type	2	03	null	null	null
NHSNumberStatusIndicatorCode_Withheld_Type	ns:AlphaNumeric_Type	2	04	null	null	null
NHSNumberStatusIndicatorCode_Withheld_Type	ns:AlphaNumeric_Type	2	05	null	null	null
NHSNumberStatusIndicatorCode_Withheld_Type	ns:AlphaNumeric_Type	2	06	null	null	null
NHSNumberStatusIndicatorCode_Withheld_Type	ns:AlphaNumeric_Type	2	07	null	null	null
NHSNumberStatusIndicatorCode_Withheld_Type	ns:AlphaNumeric_Type	2	08	null	null	null
CDSOrganisationIdentifier_Type	ns:AlphaNumeric_Type	null	null	5	3	null
Number_Type	xs:string	null	null	null	null	[0-9]{10}