How to parse XML which is stored in a column in SQL Server

Please try the following solution.

It does the following:

• Removes XML prolog with the UTF-8 encoding. SQL Server implicitly converts any XML internally to UTF-16.
• Converts NVARCHAR() data type to XML data type. After that XQuery methods to handle XML become available.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, col NVARCHAR(MAX));
INSERT INTO @tbl (col) VALUES
(N'<?xml version="1.0" encoding="UTF-8"?>
<propertylist id="root">
    <property expanded="Y" id="createsdi" selectedindex="0" type="collection">
        <collection>
            <propertylist id="1305619640064">
                <property expanded="Y" id="columnvalues" selectedindex="0"
                          type="collection">
                    <collection>
                        <propertylist id="1396440519721"/>
                    </collection>
                </property>
                <property expanded="Y" id="tests" selectedindex="0"
                          type="collection">
                    <collection>
                        <propertylist id="p1602059752707" sequence="1000000">
                            <property id="id" type="simple"><![CDATA[item1]]></property>
                            <property id="workitemid" type="simple"><![CDATA[SOURAVTESTER|1]]></property>
                        </propertylist>
                    </collection>
                </property>
                <property expanded="Y" id="specs" selectedindex="0"
                          type="collection">
                    <collection>
                        <propertylist id="p1602059825237" sequence="1000000">


                            <property id="id" type="simple"><![CDATA[item1]]></property>
                            <property id="specid" type="simple"><![CDATA[EMSpec|1]]></property>
                        </propertylist>
                    </collection>
                </property>
            </propertylist>
        </collection>
    </property>
    <property expanded="Y" id="workorder" type="propertylist">
        <propertylist id="root_0">
            <property expanded="Y" id="graceperiod" type="propertylist">
                <propertylist id="root_0_workorder_0">
                    <property expanded="Y" id="graceperiod" type="propertylist">
                        <propertylist id="root_0_workorder_0_graceperiod_0"/>
                    </property>
                    <property expanded="Y" id="deviation" type="propertylist">
                        <propertylist id="root_0_workorder_0_graceperiod_0"/>
                    </property>
                </propertylist>
            </property>
        </propertylist>
    </property>
</propertylist>')
, (NULL);
-- DDL and sample data population, end

--DECLARE @startPos INT = LEN('<?xml version="1.0" encoding="utf-8"?>') + 1;
DECLARE @prolog NVARCHAR(100) = '<?xml version="1.0" encoding="utf-8"?>';

;WITH rs AS
(
    SELECT ID
        --, TRY_CAST(SUBSTRING(col
        --  , @startPos
        --  , LEN(col)) AS XML) AS xmldata
        , TRY_CAST('<root>' + REPLACE(col, @prolog, '') + '</root>' AS XML) AS xmldata  FROM @tbl
)
SELECT ID
    , c.value('(./text())[1]', 'VARCHAR(100)') AS Item
FROM rs
    CROSS APPLY xmldata.nodes('//property[@id=("specid","workitemid")]') AS t(c);

Output

+----+----------------+
| ID |      Item      |
+----+----------------+
|  1 | SOURAVTESTER|1 |
|  1 | EMSpec|1       |
+----+----------------+