XML Parsing

Question

XML Parsing

Bobby P 271

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...

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.

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2021-07-27T16:37:53.533+00:00

There may or may not be an easy way. Right now, I don't find it easy to understand what you want to do. Can you give examples with some complete documents and how they should be transformed.
Yitzhak Khabinsky 27,196 Reputation points

2021-07-27T21:19:25.75+00:00

@Bobby P ,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
EchoLiu-MSFT 14,626 Reputation points

2021-07-28T08:02:00.157+00:00

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.

2 answers

Your answer

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2021-07-27T16:37:53.533+00:00

There may or may not be an easy way. Right now, I don't find it easy to understand what you want to do. Can you give examples with some complete documents and how they should be transformed.
Yitzhak Khabinsky 27,196 Reputation points

2021-07-27T21:19:25.75+00:00

@Bobby P ,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
EchoLiu-MSFT 14,626 Reputation points

2021-07-28T08:02:00.157+00:00

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.

Answer 1

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                   |  
+-------------------------------------------------+

Answer 2

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]

Share via

XML Parsing

2 answers

Your answer