XML parsing with namespace SQL Server

Question

We are cleaning up data in our database and a column has XML details inside of it which we want to be able to convert into plain text.

Below is the sample XML in the table column.

 <FlowDocument PagePadding="5,5,5,5" Name="RTDocument" AllowDrop="True" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation">
     <Paragraph>FILE DESTROYED - MAY 21st, 2008</Paragraph>
     <Paragraph>todo</Paragraph>
 </FlowDocument>

I am using this query, but it is not rendering the desired output due to the presence of Namespace (if I remove the namespace from the XML, I am able to render the output successfully).

SELECT  
    CAST(CAST(Comments AS XML).query('data(/FlowDocument/Paragraph)') AS VARCHAR(7000)) AS activity 
FROM 
    dbo.Activities
WHERE 
    ActivityID = 1

Kindly help in this matter.

Thanks

Community · Accepted Answer · 2017-05-23 12:23:44Z

3

You can also declare your namespace like this:

;WITH xmlnamespaces(DEFAULT 'http://schemas.microsoft.com/winfx/2006/xaml/presentation')
SELECT
CAST(CAST(Comments AS XML).query('data(/FlowDocument/Paragraph)') AS VARCHAR(7000)) AS activity 
FROM [dbo].Activities where ActivityID=1

Other options are given here: parsing xml using sql server

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Mar 4, 2016 at 19:06

Roberto

5332 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anton · Accepted Answer · 2016-03-04 19:00:18Z

2

You need to use namespace declaration in your Query as per: https://msdn.microsoft.com/en-us/library/ms191474.aspx

so your query portion would look something like:

query('
declare namespace NS="http://schemas.microsoft.com/winfx/2006/xaml/presentation";
data(/NS:FlowDocument/NS:Paragraph)
')

answered Mar 4, 2016 at 19:00

Anton

4,0902 gold badges15 silver badges32 bronze badges

1 Comment

Anton Over a year ago

You are very welcome. Please consider accepting the answer.

Collectives™ on Stack Overflow

XML parsing with namespace SQL Server

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related