question

BobbyP-1695 avatar image
0 Votes"
BobbyP-1695 asked EchoLiu-msft commented

XML Parsing

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.

sql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

@BobbyP-1695,

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;)

0 Votes 0 ·

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.

0 Votes 0 ·
BobbyP-1695 avatar image
0 Votes"
BobbyP-1695 answered

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]
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @BobbyP-1695,

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.