Returning XML Data in Tabular format when it isn't stored as XML

Question

I have this XML data stored in a varchar(8000) column in a SQL Table, unfortunately not as an XML column.

<?xml version="1.0" encoding="utf-16"?>
<ArrayOfRate xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Rate>
        <SituationID>1</SituationID>
        <SituationDescription>S</SituationDescription>
        <PaymentFortnight>8.40</PaymentFortnight>
        <PaymentRentLessThan>4.00</PaymentRentLessThan>
        <PaymentRentMoreThan>5.20</PaymentRentMoreThan>
    </Rate>
    <Rate>
        <SituationID>2</SituationID>
        <SituationDescription>M</SituationDescription>
        <PaymentFortnight>9.40</PaymentFortnight>
        <PaymentRentLessThan>5.00</PaymentRentLessThan>
        <PaymentRentMoreThan>6.20</PaymentRentMoreThan>
    </Rate>
</ArrayOfRate>

Despite it being a varchar column, is there a way I can get the data out into tabular format? There are many but I've just shown 2 as a sample.

Such as the following:

SituationID	SituationDescription	PaymentFortnight	PaymentRentLessThan	PaymentRentMoreThan
1	S	8.40	4.00	5.20
2	M	9.40	5.00	6.20

I tried the following with just one of the columns as a start:

select
    a.b.value('Rate[1]/SituationID[1]','varchar(10)') as [ID]
FROM TableName.Rate.nodes('ArrayOfRate') a(b)

but get the following errors:

Msg 107, Level 15, State 1, Line 40 The column prefix 'TableName' does not match with a table name or alias name used in the query.

Msg 9506, Level 16, State 1, Line 37 The XMLDT method 'nodes' can only be invoked on columns of type xml.

Answer 1

You need to cast it to xml before you can use XML functions on it.

But because it uses a UTF-16 encoding preamble, you would need to cast it first to nvarchar. This in turn implies it should have been kept as nvarchar at the very least anyway, and best to store it as xml.

You are also shredding at the wrong level, you need to descend to Rate in the nodes function.

SELECT
  SituationID = x1.Rate.value('(SituationID/text())[1]', 'int')
FROM Tablename tn
CROSS APPLY (
    SELECT CAST(CAST(tn.Rate AS nvarchar(max)) AS xml) AS xmlCol
) v
CROSS APPLY v.xmlCol.nodes('ArrayOfRate/Rate') x1(Rate);

db<>fiddle