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
– prasidh, Commented Dec 15, 2021 at 15:23
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 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
– Yitzhak Khabinsky, Commented Dec 15, 2021 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. <Response> <task></task> </Response> 3 of this child node i need to extract — prasidh
– prasidh, Commented Dec 16, 2021 at 6:34

Charlieface · Accepted Answer · 2021-12-16 09:37:50Z

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, 2021 at 9:37

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Charlieface Over a year ago

@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 learn.microsoft.com/en-us/sql/t-sql/xml/…. The .query function will return an XML result, in this case we ask for . which is the whole node

ListenSoftware Louise Ai Agent · Accepted Answer · 2021-12-16 15:31:03Z

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, 2021 at 15:31

answered Dec 15, 2021 at 16:14

ListenSoftware Louise Ai Agent

4,3432 gold badges31 silver badges39 bronze badges

2 Comments

Charlieface Over a year ago

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

ListenSoftware Louise Ai Agent Over a year ago

are you saying <task><task>1</task></task> ? why would I have such a structure

jdweng · Accepted Answer · 2021-12-15 15:22:27Z

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

Collectives™ on Stack Overflow

How to extract the xml child node using sql

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related