SQL Server parse XML namespace

Question

I'm trying to parse the XML below in SQL Server and dealing with the nodes in the below:

<PrebillMarkup xmlns="clr-namespace:Xyz.Query.ViewModels;assembly=Xyz.Query" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">  
    <PrebillMarkup.NewValues>   
        <x:String x:Key="Narrative">Calls with P.</x:String>
    </PrebillMarkup.NewValues> 
    <PrebillMarkup.OriginalValues>   
        <x:String x:Key="Narrative">Calls with P on confi agreement.</x:String>   
    </PrebillMarkup.OriginalValues>
</PrebillMarkup>

I don't have any SQL yet from all of my research since I can't seem to find any namespaces that come close to what I'm trying to parse. Intended output is:

Original Values | New Values
_________________________________________________
Call with P     | Calls with P on confi agreement

Yitzhak Khabinsky · Accepted Answer · 2021-12-17 21:40:14Z

It is very similar to the @DavidBrowne-Microsoft solution, just performance optimized.

SQL

DECLARE @xml XML = 
N'<PrebillMarkup xmlns="clr-namespace:Xyz.Query.ViewModels;assembly=Xyz.Query" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">
    <PrebillMarkup.NewValues>
        <x:String x:Key="Narrative">Calls with P.</x:String>
    </PrebillMarkup.NewValues>
    <PrebillMarkup.OriginalValues>
        <x:String x:Key="Narrative">Calls with P on confi agreement.</x:String>
    </PrebillMarkup.OriginalValues>
</PrebillMarkup>';

WITH XMLNAMESPACES(DEFAULT 'clr-namespace:Xyz.Query.ViewModels;assembly=Xyz.Query'
, 'http://schemas.microsoft.com/winfx/2006/xaml' AS x)
SELECT c.value('(PrebillMarkup.NewValues/x:String/text())[1]', 'VARCHAR(100)') AS [Original Values]
    , c.value('(PrebillMarkup.OriginalValues/x:String/text())[1]', 'VARCHAR(100)') AS [New Values]
FROM @xml.nodes('/PrebillMarkup') AS t(c);

Output

+-----------------+----------------------------------+
| Original Values |            New Values            |
+-----------------+----------------------------------+
| Calls with P.   | Calls with P on confi agreement. |
+-----------------+----------------------------------+

David Browne - Microsoft · Accepted Answer · 2021-12-17 21:32:39Z

You need an with xmlnamespaces declaration on the query. eg

declare @doc xml = '
<PrebillMarkup xmlns="clr-namespace:Xyz.Query.ViewModels;assembly=Xyz.Query" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">  
    <PrebillMarkup.NewValues>   
        <x:String x:Key="Narrative">Calls with P.</x:String>
    </PrebillMarkup.NewValues> 
    <PrebillMarkup.OriginalValues>   
        <x:String x:Key="Narrative">Calls with P on confi agreement.</x:String>   
    </PrebillMarkup.OriginalValues>
</PrebillMarkup>
';

with xmlnamespaces ('http://schemas.microsoft.com/winfx/2006/xaml' as x,
                    default 'clr-namespace:Xyz.Query.ViewModels;assembly=Xyz.Query')
select @doc.value('(/PrebillMarkup/PrebillMarkup.OriginalValues/x:String)[1]','nvarchar(50)') [Original Values],
       @doc.value('(/PrebillMarkup/PrebillMarkup.NewValues/x:String)[1]','nvarchar(50)') [New Values]

outputs

Original Values                                    New Values
-------------------------------------------------- --------------------------------------------------
Calls with P on confi agreement.                   Calls with P.

(1 row affected)

By convention you match the xmlnamespace aliases to the namespace aliases used in the xml doc, but it's not technically necessary. This works too:

with xmlnamespaces ('http://schemas.microsoft.com/winfx/2006/xaml' as foo,
                    'clr-namespace:Xyz.Query.ViewModels;assembly=Xyz.Query' as bar)
select @doc.value('(/bar:PrebillMarkup/bar:PrebillMarkup.OriginalValues/foo:String)[1]','nvarchar(50)') [Original Values],
       @doc.value('(/bar:PrebillMarkup/bar:PrebillMarkup.NewValues/foo:String)[1]','nvarchar(50)') [New Values]

Collectives™ on Stack Overflow

SQL Server parse XML namespace

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related