CODEWITHSUNDEEP
t-sql
Gurarpan
Gurarpan

Reputation: 61

TSQL to Concatenate XML output

I have an XML column in the SQL database and the value for XML is

<m>
  <t>1</t>
   <E>
     <I>x </I>
     <D>ErrorDescription1</D>
   </E>
</m>
<m>
  <t>2</t>
    <E>
      <I>x </I>
       <D>ErrorDescription2</D>
    </E>
 </m>

I need the output in a string as: x ErrorDescription1;y ErrorDescription2 .

Can you please guide how to get this output?

Upvotes: 0

Views: 93

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

One more approach was a FLWOR-query:

DECLARE @xml XML = 
N'<m>
  <t>1</t>
   <E>
     <I>x </I>
     <D>ErrorDescription1</D>
   </E>
</m>
<m>
  <t>2</t>
    <E>
      <I>y </I>
       <D>ErrorDescription2</D>
    </E>
 </m>';

--The query

 SELECT @xml.query(N'for $m in /m
                     let $t:=($m/t/text())[1] cast as xs:int?
                     let $i:=($m/E/I/text())[1]
                     let $d:=($m/E/D/text())[1]
                     order by $t
                     return  <a>{concat($i," ",$d,";")}</a>')
            .value('.','nvarchar(max)');

The idea in short:

  • We pick the repeating <m> elements into a variable loop
  • We use - for better readability - let statements to push the values into variables
  • We can use order by (if needed)
  • We use a trick with XML by returning the concatenated value within an element and
  • then pick the whole content using just a '.' as XPath.

The only thing left for you ist the final semicolon. You can either put it in front and use STUFF() to cut away the first letter, or you can use SUBSTRING() together with LEN() to cut the last letter off. Or you you might just leave it in place :-)

As in most cases there is a (not recommended) short version too:

 SELECT @xml.query(N'for $e in /m/E
                     return  <a>{concat(($e/I)[1]," ",($e/D)[1],";")}</a>')
            .value('.','nvarchar(max)')

Upvotes: 1

gotqn
gotqn

Reputation: 43626

Try something like this:

DECLARE @xml XML = '<m>
  <t>1</t>
   <E>
     <I>x </I>
     <D>ErrorDescription1</D>
   </E>
</m>
<m>
  <t>2</t>
    <E>
      <I>y </I>
       <D>ErrorDescription2</D>
    </E>
 </m>';

The following statement will give you the details in tabular view:

SELECT  ROW_NUMBER() OVER(ORDER BY T.c)
    ,T.c.value('(./I)[1]', 'varchar(4)')
    ,T.c.value('(./D)[1]', 'varchar(128)')
FROM @xml.nodes('m/E') T(c);

enter image description here

Then, concatenate then:

 WITH DataSource ([RowID], [value]) AS
 (
     SELECT  ROW_NUMBER() OVER(ORDER BY T.c)
            ,T.c.value('(./I)[1]', 'varchar(4)') + T.c.value('(./D)[1]', 'varchar(128)')
     FROM @xml.nodes('m/E') T(c)
 )
 SELECT MAX(CASE WHEN [RowID] = 1 THEN [value] END) + ';'  + MAX(CASE WHEN [RowID] = 2 THEN [value] END)
 FROM DataSource

Upvotes: 1

Related Questions