SQL Server parse XML namespace-CodePudding

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

CodePudding user response：

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]

CodePudding user response：

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