Extract data from a table with XML column in SQL server

Question

I've the below sample XML saved in a table called CE_ExtendedElements and column called xmlValue

<SIF_ExtendedElements xmlns="">
  <SIF_ExtendedElement Name="EmergencyNumber">00000000</SIF_ExtendedElement>
  <SIF_ExtendedElement Name="EmergencyNumber">00000000</SIF_ExtendedElement>
  <SIF_ExtendedElement Name="Qualifications">
  <Qualification>
    <Qualification>Bsc</Qualification>
    <QualificationWhenHired>True</QualificationWhenHired>
    <Major>Compuetr</Major>
    <GraduationYear>1993</GraduationYear>
    <Institution>Home Univ</Institution>
    <CountryID>1234</CountryID>
  </Qualification>
  <Qualification>
    <Qualification>Bsc</Qualification>
    <QualificationWhenHired>True</QualificationWhenHired>
    <Major>Compuetr</Major>
    <GraduationYear>1993</GraduationYear>
    <Institution>Home Univ</Institution>
    <CountryID>1234</CountryID>
  </Qualification>
</SIF_ExtendedElement>
  <SIF_ExtendedElement Name="JoinDate">2003-09-15T00:00:00</SIF_ExtendedElement>
</SIF_ExtendedElements>

The maximum nodes for any item is 2 only (the user can add 2 EmergencyNumber and 2 Qualifications for each record).

Can somebody help me in reading the above XML using SQL statement to convert the XML nodes as columns? Same as the below table:

Thanks

First things first. Create your table. Then post the create table sql code in your question. I'll build your query. So, you have a table called ce_ExtendedElements that possess one column of type xml? That's the first step required to be able to parse it. — WickedFan
– WickedFan, Commented Apr 27, 2016 at 17:23
You need to dynamically create columns EmergencyNumber1, EmergencyNumber2, etc.? Or they are already there in the table? — Alexander Petrov
– Alexander Petrov, Commented Apr 27, 2016 at 17:29

WickedFan · Accepted Answer · 2016-04-27 17:43:13Z

1

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)


SELECT @XML = XMLvalue FROM CE_ExtendedElements


EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


SELECT EmergencyNumber1, EmergencyNumber2, Qualification, QualificationWhenHired, Major, Institution, CountryID
FROM OPENXML(@hDoc, 'SIF_ExtendedElements/Qualifications/Qualification')
WITH 
(
  EmergencyNumber1 [varchar](50) '../../@EmergencyNumber1',
  EmergencyNumber2 [varchar](50) '../../@EmergencyNumber2',
  Qualification [varchar](100) '@Qualification',
  QualificationWhenHired [varchar](50) '@QualificationWhenHired',
  Major [varchar](50) '@Major',
  Institution [varchar](50) '@Institution',
  CountryID [varchar](50) '@CountryID'
)


EXEC sp_xml_removedocument @hDoc
GO

This should be pretty close!

answered Apr 27, 2016 at 17:43

WickedFan

3562 silver badges12 bronze badges

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

8 Comments

WickedFan Over a year ago

Also.. you'll have to probably change the xml value for emergencynumber to make them distinct. It doesn't appear to be a valid xml format.

WickedFan Over a year ago

Furthermore, with xml, you can't have them merged within the same line with "dynamics" column names (major 1,2,3 etc). It'll provide you with a single line for all items. If you have two qualification, that person will be represented on two lines.

Gottfried Lesigang Over a year ago

SELECT FROM OPENXML with the procedure calls to prepare and remove the document is absolutely outdated...

WickedFan Over a year ago

Yah eh? SQL2005 is a bit old. But it's still supported in SQL Server 2016 (also needed for MCSE 70-462)

Gottfried Lesigang Over a year ago

Still supported doesn't mean you should use it... There are some rare performance related situations where FROM OPENXML is still a good choice (especially if you want to read from the same XML more often), but new solutions should use the much better performing and much easier to handle XML methods with XPath and XQuery ...

|

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

I must say, that the structure of your XML seems to be not the best possible...

But you might try it like this:

Attention, used a dummy-namespaces and changed the data for better control

DECLARE @xml XML=
'<SIF_ExtendedElements xmlns="http://dummy_uri">
  <SIF_ExtendedElement Name="EmergencyNumber">11111111</SIF_ExtendedElement>
  <SIF_ExtendedElement Name="EmergencyNumber">22222222</SIF_ExtendedElement>
  <SIF_ExtendedElement Name="Qualifications">
    <Qualification>
      <Qualification>Bsc1</Qualification>
      <QualificationWhenHired>True</QualificationWhenHired>
      <Major>Compuetr</Major>
      <GraduationYear>1991</GraduationYear>
      <Institution>Home Univ</Institution>
      <CountryID>1111</CountryID>
    </Qualification>
    <Qualification>
      <Qualification>Bsc2</Qualification>
      <QualificationWhenHired>True</QualificationWhenHired>
      <Major>Compuetr</Major>
      <GraduationYear>1992</GraduationYear>
      <Institution>Home Univ</Institution>
      <CountryID>2222</CountryID>
    </Qualification>
  </SIF_ExtendedElement>
  <SIF_ExtendedElement Name="JoinDate">2003-09-15T00:00:00</SIF_ExtendedElement>
</SIF_ExtendedElements>';

WITH XMLNAMESPACES(DEFAULT 'http://dummy_uri')
SELECT Elmt.value('(SIF_ExtendedElement[@Name="EmergencyNumber"])[1]','varchar(max)') AS EmergencyNumber1
      ,Elmt.value('(SIF_ExtendedElement[@Name="EmergencyNumber"])[2]','varchar(max)') AS EmergencyNumber2
      ,Qlfct.value('Qualification[1]/Qualification[1]','varchar(max)') AS Qualification1 
      ,Qlfct.value('Qualification[1]/QualificationWhenHired[1]','bit') AS QualificationWhenHired1 
      ,Qlfct.value('Qualification[1]/Major[1]','varchar(max)') AS Major1 
      ,Qlfct.value('Qualification[1]/GraduationYear[1]','int') AS GraduationYear1 
      ,Qlfct.value('Qualification[1]/Institution[1]','varchar(max)') AS Institution1 
      ,Qlfct.value('Qualification[1]/CountryID[1]','int') AS CountryID1 
      ,Qlfct.value('Qualification[2]/Qualification[1]','varchar(max)') AS Qualification2
      ,Qlfct.value('Qualification[2]/QualificationWhenHired[1]','bit') AS QualificationWhenHired2 
      ,Qlfct.value('Qualification[2]/Major[1]','varchar(max)') AS Major2
      ,Qlfct.value('Qualification[2]/GraduationYear[1]','int') AS GraduationYear2
      ,Qlfct.value('Qualification[2]/Institution[1]','varchar(max)') AS Institution2 
      ,Qlfct.value('Qualification[2]/CountryID[1]','int') AS CountryID2 
FROM @xml.nodes('/SIF_ExtendedElements') AS A(Elmt)
CROSS APPLY Elmt.nodes('SIF_ExtendedElement[@Name="Qualifications"]') AS B(Qlfct)

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 27, 2016 at 21:10

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

3 Comments

AbuYazan Over a year ago

This is exactly what I'm looking for.

Gottfried Lesigang Over a year ago

@AbuYazan Glad to read this and thx for the acceptance one tiny hint: Below the other answer you say "thank you" this is very kind of you, but it would be even kinder to vote all good contributions up. This is the SO-way to say thank you... and it is the measure tool of quality... and it is the fuel SO is running with... I just visited your profile and found, that you did not raise one single vote up to now... May I ask you to start to vote whereever something good crosses your path? Happy Coding!

AbuYazan Over a year ago

Actually I was not aware of this:) Will start doing that immediately.

Collectives™ on Stack Overflow

Extract data from a table with XML column in SQL server

2 Answers 2

8 Comments

Attention, used a dummy-namespaces and changed the data for better control

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Attention, used a dummy-namespaces and changed the data for better control

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related