parsing xml using sql server

Question

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

below is the XML <FlowDocument PagePadding="5,5,5,5" Name="RTDocument" AllowDrop="True" xmlns="schemas.microsoft.com/winfx/2006/xaml/presentation"> <Paragraph>Licence Number: 04467</Paragraph><Paragraph>Licence Number: dddd</Paragraph></FlowDocument> — Sidharth Soneja
– Sidharth Soneja, Commented Mar 2, 2016 at 18:07
Please put the XML in your question, not in the comments. Also: stackoverflow.com/help/how-to-ask — Tom H
– Tom H, Commented Mar 2, 2016 at 18:10
Hi @SidharthSoneja if this or any answer has solved your question please consider accepting it 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
– Roberto, Commented Apr 21, 2016 at 18:14

Roberto · Accepted Answer · 2016-03-02 19:07:21Z

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)

answered Mar 2, 2016 at 19:07

Roberto

5332 silver badges10 bronze badges

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

Comments

René · Accepted Answer · 2016-03-02 20:01:15Z

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

answered Mar 2, 2016 at 20:01

René

335 bronze badges

Collectives™ on Stack Overflow

parsing xml using sql server

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related