XQUERY() for SQL Assistance

Question

I have a table that stores the data in XML. There are two nodes that I need the data from.

Example script:

select MARKUP_MESSAGE  
from TABLE_NAME 
where row_uno = 6599064

Example result:

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

I have two nodes <PrebillMarkup.NewValues> and <PrebillMarkup.OriginalValues>.

I want to be able to pull out the x:String from the <PrebillMarkup.NewValues> to read "Review of additional policies - test"

So when I run the statement:

select MARKUP_MESSAGE  
from TABLE_NAME 
where row_uno = 6599064

the result will be "Review of additional policies - test"

Or if I do another query to pull out the text value for

<x:String x:Key="Narrative" xml:space="preserve">

Any ideas on how to do this as each time I try I'm getting more XML data but I need just plain text.

Thanks all

SQL version:

Microsoft SQL Server 2019 - 15.0.4280.7 (X64)   
Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64>

Answer 1

A minimal reproducible example is not provided. So, I am shooting from the hip.

As @ThomA already pointed out, you need to declare and use namespaces.

XQuery .nodes() and .value() methods do the job to shred the XML into rectangular/relational format.

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

row_uno	element_name	attribute_key	string_value
1	PrebillMarkup.NewValues	Narrative	Review of policies against previous policies
1	PrebillMarkup.OriginalValues	Narrative	Review of policies against previous policies sdffddgsgfdfdg