CODEWITHSUNDEEP
sqlsql-server-2008sqlxml
Sidharth Soneja
Sidharth Soneja

Reputation: 31

parsing xml using sql server

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

Upvotes: 2

Views: 166

Answers (2)

Ren&#233;
Ren&#233;

Reputation: 33

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

Upvotes: 3

Roberto
Roberto

Reputation: 533

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)

Upvotes: 3

Related Questions