I have a few XSD files that define a report, wondering if there's a way load this into a table that I can query.

The elements XSD defines the required fields, it looks like:

  <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>

I need to convert it to

     Field_Name         Type                    Length        enumeration 
     NHSNumber...       AlphaNumeric_Type        2                 01
     NHSNumber...       AlphaNumeric_Type        2                 02
     NHSNumber...       AlphaNumeric_Type        2                 03

and so on

CodePudding user response：

You can load it into an XML variable and then use various SQL Server XML methods to select the nodes of interest and extract the values. See xml Data Type Methods and Path Expressions (XQuery) for more information.

In the following, I wrapped your XSD fragment in a "schema" element that also defined the "xs:" namespace prefix.

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:schema >'

SELECT
    N.node.value('(../../@name)[1]', 'nvarchar(max)') AS Field_Name,
    N.node.value('(../@base)[1]', 'nvarchar(max)') AS Type,
    N.node.value('(../xs:length/@value)[1]', 'int') AS Length,
    N.node.value('@value[1]', 'nvarchar(max)') AS enumeration
FROM @xml.nodes('//xs:enumeration') N(node)

The .nodes() method selects all of the enumeration nodes from the supplied xml. The leading // selects nodes at any depth. The N(nodes) provide arbitrary table and column aliases to the selected results. The .value() method is then used to select attributes values from either the selected node, parent node, or sibling node in the case of Length. The @ prefix on a name refers to an attribute instead of a child node. Because many XPath selectors could potentially select more than one value, you often need to use [1] or (...)[1] to select just the first.

Result:

See this db<>fiddle.

The XSD can also be loaded into an XSD typed column of a table and accessed in a manner similar to the above by replacing @xml with the column reference.

The above is coded for this specific enumeration type scenario. If your actual XSD contains other type definitions that don't follow this pattern, you may need to handle those cases separately.