2

Needed some help to parse text in Paragraph element in following XML in SQL server.

<FlowDocument PagePadding="5,5,5,5" Name="RTDocument" AllowDrop="True" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation">   
  <Paragraph>Licence Number: 04467</Paragraph>
  <Paragraph>Licence Number: 3535333</Paragraph>
</FlowDocument>

Please share any queries you may have.

Thank you

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
Sidharth Soneja
  • 31
  • 1
  • below is the XML Licence Number: 04467Licence Number: dddd – Sidharth Soneja Mar 02 '16 at 18:07
  • 2
    Please put the XML in your question, not in the comments. Also: http://stackoverflow.com/help/how-to-ask – Tom H Mar 02 '16 at 18:10
  • Hi @SidharthSoneja if this or any answer has solved your question please consider [accepting it](http://meta.stackexchange.com/q/5234/179419) by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this. – Roberto Apr 21 '16 at 18:14

2 Answers2

3

One way of doing this is: (If they have the same namespace)

;with xmlnamespaces(default 'schemas.microsoft.com/winfx/2006/xaml/presentation')
select @xml.value('(/FlowDocument/Paragraph)[1]', 'varchar(max)') + ' ' + 
    @xml.value('(/FlowDocument/Paragraph)[2]', 'varchar(max)')

Another way:

select data.col.value('(*:Paragraph)[1]','varchar(100)') 
    + ' ' +  data.col.value('(*:Paragraph)[2]','varchar(100)') as ParamName
FROM @xml.nodes('(*:FlowDocument)') as data(col)
Roberto
  • 533
  • 2
  • 10
3

Or (complementing Roberto answer)

;WITH XMLNAMESPACES(DEFAULT 'schemas.microsoft.com/winfx/2006/xaml/presentation')

SELECT  FlowDocument.Paragraph.value('.', 'varchar(MAX)')
FROM    @xml.nodes('//FlowDocument/Paragraph') AS FlowDocument(Paragraph)

If has many Paragraph tags

René
  • 33
  • 5