How to get the XML Node's elements and properties from an XML format using TSQL

Question

I have an XML format that looks like this:

<Properties>
  <Property name="AllowAccess" type="ComboBox" displayName="Contracts:" default="0" tabs="1" parent="SystemUsersContainer" labelWidth="228" allowLimit="true">
    <item key="None" value="0" />
    <item key="Read Only" value="1" />
    <item key="Create, Read, and Update" value="7" />
    <item key="All" value="15" />
  </Property>
  <Property name="Allotment" type="ComboBox" displayName="Allotments:" default="0" tabs="2" parent="AllowAccess" labelWidth="228" allowLimit="true">
    <item key="None" value="0" />
    <item key="Read Only" value="1" />
    <item key="Create, Read, and Update" value="7" />
    <item key="All" value="15" />
  </Property>
</Properties>

Im trying to make a query to return this format:

ID	PropertyName	PropertyType	DisplayName
1	AllowAccess	ComboBox	Contracts
1	Allotment	ComboBox	Allotments
2	etc	etc	etc

The XML Format above is for ID 1.

I'm not sure how to start or what specific question to ask in Google as I usually always work on listing down the child nodes like item key, but this time, the elements on the line are what I need to display and save in a table.

What do we call these part? Are these still elements of 1 node? - Property name="AllowAccess" type="ComboBox" displayName="Contracts:" default="0" tabs="1" parent="SystemUsersContainer" labelWidth="228" allowLimit="true"

any help is appreciated

I have tried this code from before that lists down the Item Key but it's giving me a blank

                    DECLARE @XML XML
                    
                    SET @XML = (SELECT XMLData FROM dbo.Properties  where ID = 1)
            
                        ;with cte as(
                                SELECT
                                    1 AS ID,
                                    T.C.value('local-name(.)', 'nvarchar(max)') as Nodes
                                FROM @XML.nodes('Properties/Property/*') as T(C)
                            
                        )
                         SELECT * FROM CTE

Answer 1

Please try the following solution.

What do we call these part? Are these still elements of 1 node? - Property name="AllowAccess" type="ComboBox" displayName="Contracts:" default="0" tabs="1" parent="SystemUsersContainer" labelWidth="228" allowLimit="true"

They are called XML attributes. We are referring to them by adding '@' in the XPath expressions and XQuery.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, XMLData XML);
INSERT @tbl (XMLData) VALUES
(N'<Properties>
  <Property name="AllowAccess" type="ComboBox" displayName="Contracts:" default="0" tabs="1" parent="SystemUsersContainer" labelWidth="228" allowLimit="true">
    <item key="None" value="0" />
    <item key="Read Only" value="1" />
    <item key="Create, Read, and Update" value="7" />
    <item key="All" value="15" />
  </Property>
  <Property name="Allotment" type="ComboBox" displayName="Allotments:" default="0" tabs="2" parent="AllowAccess" labelWidth="228" allowLimit="true">
    <item key="None" value="0" />
    <item key="Read Only" value="1" />
    <item key="Create, Read, and Update" value="7" />
    <item key="All" value="15" />
  </Property>
</Properties>');
-- DDL and sample data population, end

SELECT ID
    , c.value('@name','VARCHAR(30)') AS PropertyName
    , c.value('@type','VARCHAR(30)') AS PropertyType
    , c.value('@displayName','VARCHAR(30)') AS DisplayName
FROM @tbl
CROSS APPLY XMLData.nodes('/Properties/Property') AS t(c)
WHERE ID = 1;

Output

ID	PropertyName	PropertyType	DisplayName
1	AllowAccess	ComboBox	Contracts:
1	Allotment	ComboBox	Allotments: