Convert XML data into a table SQL, Different tag in one script

Question

I have one XML data which I want to bring them into a one table the XML data is like this:

<return>
<start>
  <name>Sara</name>
  <familyname>Moradi</familyname>
  <age>22</age>
</start>
<start>
  <name>Sam</name>
  <familyname>Mic</familyname>
  <age>32</age>
</start>
<errorCode>0</errorCode>
<resultStatus/>
<extra>22255</extra>
</return>

and I wanna create a table like this:

name	familyname	age	errorCode	extra
Sara	Moradi	22	0	22255
Sam	Mic	32	0	22255

I check the previous ones but they didn't help me.

What have you tried? What is the error, problem? Please edit the question and show your code. — Serg
– Serg, Commented Jul 4, 2021 at 7:12
the second part which is start from <errorCode> I can't bring it to my output. — Sara Moradi
– Sara Moradi, Commented Jul 4, 2021 at 7:38

PareshK · Accepted Answer · 2021-07-04 08:31:49Z

3

Try below:

declare @data xml = convert(xml, '<return>
    <start>
      <name>Sara</name>
      <familyname>Moradi</familyname>
      <age>22</age>
    </start>
    <start>
      <name>Sam</name>
      <familyname>Mic</familyname>
      <age>32</age>
    </start>
    <errorCode>0</errorCode>
    <resultStatus/>
    <extra>22255</extra>
    </return>')
    
    SELECT X.Y.value('(name)[1]', 'VARCHAR(20)') as name,
           X.Y.value('(familyname)[1]', 'VARCHAR(20)')   as familyname,
           X.Y.value('(age)[1]', 'int') as age,
           A.B.value('(errorCode)[1]','int') as errorCode,
           A.B.value('(extra)[1]','int') as extra
    FROM  @data.nodes('return') as A(B)
    cross apply A.B.nodes('start') as X(Y)

For quick solution, I have stored xml data into variable, you need to replace it with your original column from table.

answered Jul 4, 2021 at 8:31

PareshK

721 silver badge4 bronze badges

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

2 Comments

Sara Moradi Over a year ago

what is [1] in front of columns name?

PareshK Over a year ago

It identifies which node value to retrieve if there are multiple node with same name inside the parent node. As a standard format, there will be always 1 unique child node under parent node. So it will always get the first node values once it finds under parent node.

Collectives™ on Stack Overflow

Convert XML data into a table SQL, Different tag in one script

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related