1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sidharth Soneja
  • 31
  • 1

2 Answers2

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

Community
  • 1
  • 1
Roberto
  • 533
  • 2
  • 10
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)
')
Anton
  • 3,587
  • 2
  • 12
  • 27
  • You are very welcome. Please consider accepting the answer. – Anton Mar 04 '16 at 19:33
  • @SidharthSoneja If this answers your question to your satisfaction, you should check the ✔ next to the answer. Read more about this etiquette [here](https://stackoverflow.com/help/someone-answers). – TT. Mar 04 '16 at 21:18