CODEWITHSUNDEEP
sqlsql-serverxml
Sara Moradi
Sara Moradi

Reputation: 35

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

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.

Upvotes: 0

Views: 168

Answers (1)

PareshK
PareshK

Reputation: 72

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.

Upvotes: 3

Related Questions