How to extract the xml child node using sql

Question

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.

I can take values of each node using select command from xml, but i am not able to extract the node itself. In c# i have done it. we will get the child nodes — prasidh, Dec 15 '21 at 15:23
While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/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 code 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;). All within the question, no images. — Yitzhak Khabinsky, Dec 15 '21 at 15:53
through an api I will get an xml response and i am inserting it to a db table as xml, in that xml response itself there will be several responses as child nodes, Once after inserting this xml to table later using a stored procedure i need to read the saved xml and extract each responses in it and insert it into another table. in the above example assume it as the response of api which i am inserting first. Then i need to read this xml data and extract each child node in that xml to different tables as xml. 3 of this child node i need to extract — prasidh, Dec 16 '21 at 06:34

Golden Lion · Answer 1 · 2021-12-16T15:31:03.237

1

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>

using xml path nodes and value

select X.Y.value('(task)[1]','int') task from @tmp t
cross apply t.XmlData.nodes('Responses/Response') as X(Y)

output

 task
 1
 2
 3

edited Dec 16 '21 at 15:31

answered Dec 15 '21 at 16:14

Golden Lion

3,840
2
26
35

Slight difference: yours takes each `Response` and pulls out the first `task`, mine goes straight to `task`. Theoretically if there are multiple `task` nodes you would get only the first from each `Response`, although there's no indication from OP that this is the case – Charlieface Dec 16 '21 at 16:00
are you saying 1 ? why would I have such a structure – Golden Lion Dec 16 '21 at 16:03

score 1 · Answer 2 · answered Dec 16 '21 at 09:37

1

You can use the following SQL XQuery solution:

.query will give you a whole XML node, rather than .value which only gives you a single inner value.

SELECT x.task.query('.') task
FROM @tmp t
CROSS APPLY t.XmlData.nodes('Responses/Response/task') x(task);

db<>fiddle

Output:

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

answered Dec 16 '21 at 09:37

Charlieface

52,284
6
19
43

@GoldenLion It "applies" the result of the `.nodes` Table Valued function to each row, and that function will "explode out" the nodes (the ones which matche the XQuery input) into separate rows. See the docs https://learn.microsoft.com/en-us/sql/t-sql/xml/nodes-method-xml-data-type?view=sql-server-ver15. The `.query` function will return an XML result, in this case we ask for `.` which is the whole node – Charlieface Dec 16 '21 at 15:54
thanks I used it to unpivot my xml results – Golden Lion Dec 16 '21 at 15:57

score 0 · Answer 3 · answered Dec 15 '21 at 15:22

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

How to extract the xml child node using sql

3 Answers3