用于SQL协助的XQUERY()

不提供minimal reproducible example.所以，我是随口说出来的.

正如@thoma已经指出的，您需要声明和使用名称空间.

XQuery .nodes()和.value()方法完成将XML分解成矩形/关系格式的工作.

SQL个

-- DDL and sample data population, start
DECLARE @tbl TABLE (row_uno INT IDENTITY PRIMARY KEY, Markup_Message XML);
INSERT @tbl (Markup_Message) VALUES
(N'<PrebillMarkup xmlns="clr-namespace:Aderant.Query.ViewModels;assembly=Aderant.Query"
               xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">
    <PrebillMarkup.NewValues>
        <x:String x:Key="Narrative" xml:space="preserve">Review of policies against previous policies</x:String>
    </PrebillMarkup.NewValues>
    <PrebillMarkup.OriginalValues>
        <x:String x:Key="Narrative" xml:space="preserve">Review of policies against previous policies sdffddgsgfdfdg</x:String>
    </PrebillMarkup.OriginalValues>
</PrebillMarkup>');
-- DDL and sample data population, end

WITH XMLNAMESPACES(DEFAULT 'clr-namespace:Aderant.Query.ViewModels;assembly=Aderant.Query',
                   'http://schemas.microsoft.com/winfx/2006/xaml' AS x)
SELECT t.row_uno
    , c.value('local-name(.)', 'VARCHAR(128)') AS element_name
    , c.value('(x:String/@x:Key)[1]', 'VARCHAR(128)') AS attribute_key
    , c.value('(x:String/text())[1]', 'VARCHAR(128)') AS string_value
FROM @tbl AS t
CROSS APPLY Markup_Message.nodes('/PrebillMarkup/*') AS t1(c)
WHERE row_uno = 1;

Output个