How to extract the xml child node using sql-CodePudding

I am having column of datatype xml in my database.

sample value shown below.

<Responses>
   <Response>
     <task></task>
   </Response>
  <Response>
     <task></task>
   </Response>
  <Response>
     <task></task>
   </Response>
</Responses>

So from the above xml I need to extract each node and need to save it as different row in another table. From the above example there will be 3 rows.

CodePudding user response：

Try following :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;

namespace ConsoleApplication7
{
    class Program
    {
        static void Main(string[] args)
        {
            string xml = @"<Responses>
                               <Response>
                                 <task></task>
                               </Response>
                              <Response>
                                 <task></task>
                               </Response>
                              <Response>
                                 <task></task>
                               </Response>
                            </Responses>";
            StringReader reader = new StringReader(xml);
            DataSet ds = new DataSet();
            ds.ReadXml(reader);
        }
    }
}

CodePudding user response：

try using the xml column, query. you will need to cast a string column to xml then use query. see (SQL Server - returning xml child nodes for xml column)

declare @tmp as table (ID UNIQUEIDENTIFIER,
CreatedDate DATETIME,
XmlData XML)

declare @xml as xml='<Responses>
   <Response>
     <task>1</task>
   </Response>
  <Response>
     <task>2</task>
   </Response>
  <Response>
     <task>3</task>
   </Response>
</Responses>'

insert into @tmp(CreatedDate,XmlData) values(GetDate(),@xml)


select XmlData.query('Responses/Response/task') task from @tmp

output:

<task>1</task><task>2</task><task>3</task>