XML Parsing

Question

I have XML Data that may or may not have <Section> text...

If the <Section> has text like so...

<Section>Blah Blah Blah Section Text</Section>

then I want to concatenate that <Section> Text Block with the <Section><Content> text

If the <Section> has no text, like so...

<Section>

Then I just want the <Section><Content> text block

Is there an easy way to do this?

Thanks for your review and am hopeful for a response.

Answer 1

I think this is working...

                    CASE
                        WHEN [n].[value] ('(./Section/text())[1]', 'VARCHAR(MAX)') <> ' '
                            THEN CONCAT (
                                            'WARNING: ',
                                            [n].[value] ('(./Section/text())[1]', 'VARCHAR(MAX)'),
                                            ' ',
                                            'COMMON USES: ',
                                            [n].[value] ('(./Section//Content/text())[1]', 'VARCHAR(MAX)')
                                        )
                        ELSE CONCAT (
                                        'COMMON USES: ',
                                        [n].[value] ('(./Section//Content/text())[1]', 'VARCHAR(MAX)')
                                    )
                    END         AS [Txt]

Answer 2

Hi @Bobby P ,

Here is how to do much simpler.

SQL

DECLARE @xml XML =  
N'<root>  
	<r>  
		<Section>Section text<Content>Content Text</Content></Section>  
	</r>  
	<r>  
		<Section>  
			<Content>2nd Content Text</Content>  
		</Section>  
	</r>  
</root>';  
  
SELECT CONCAT('WARNING: ' + c.value('(Section/text())[1]', 'VARCHAR(30)') + SPACE(1)  
	, 'COMMON USES: ', c.value('(Section/Content/text())[1]', 'VARCHAR(30)')) AS result  
FROM @xml.nodes('/root/r') AS t(c);  

Output

+-------------------------------------------------+  
|                     result                      |  
+-------------------------------------------------+  
| WARNING: Section text COMMON USES: Content Text |  
| COMMON USES: 2nd Content Text                   |  
+-------------------------------------------------+