Loading xml data into SQL server 2012 table

• srikanth_vin 86046

  Newbie

  Points: 8

  More actions

  February 17, 2016 at 10:47 am

  #311445

  Hi Team,

  I have an XML as below. and I am trying to load that xml to a SQL Server table using t-sql code: XML:

  <Validations>

  <ActivityID>17626</ActivityID>

  <validation id="ActivityIdPass" status="Fail">An Activity ID must be defined.</validation>

  <validation id="ActivityFormatPass" status="Pass">Passed</validation>

  <validation id="ActivityFormatSubCatPass" status="Pass">Passed</validation>

  <validation id="StartDateTimePass" status="Fail">A Release Date must be defined</validation>

  <validation id="EndDateTimePass" status="Fail">An Expiration Date must be defined</validation>

  <validation id="CityPass" status="Pass">Passed</validation>

  <validation id="StateProvincePass" status="Pass">Passed</validation>

  </Validations>

  the out put should be as a table format where the attribute "id" will be column and the node value "An Activity ID must be defined." should be as row .

  for example:

  ActivityIdPadd | ActivityFormatPass

  -------------------------------------------------------------------------------

  An Activity ID must be defined | Passed

  can anybody help me out?

• Hugo Kornelis

  SSC Guru

  Points: 64790

  More actions

  February 17, 2016 at 11:59 am

  #1859352

  Something like this?

  DECLARE @x xml = '<Validations>

  <ActivityID>17626</ActivityID>

  <validation id="ActivityIdPass" status="Fail">An Activity ID must be defined.</validation>

  <validation id="ActivityFormatPass" status="Pass">Passed</validation>

  <validation id="ActivityFormatSubCatPass" status="Pass">Passed</validation>

  <validation id="StartDateTimePass" status="Fail">A Release Date must be defined</validation>

  <validation id="EndDateTimePass" status="Fail">An Expiration Date must be defined</validation>

  <validation id="CityPass" status="Pass">Passed</validation>

  <validation id="StateProvincePass" status="Pass">Passed</validation>

  </Validations>';

  INSERT dbo.YourTable (ActivityIdPadd, ActivityFormatPass)

  SELECT n.node.value('@id[1]', 'nvarchar(20)') AS id,

  n.node.value('.[1]', 'nvarchar(50)') AS value

  FROM @x.nodes('Validations/validation') AS n(node);

  ---

  Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
  Visit my SQL Server blog: https://sqlserverfast.com/blog/
  SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
  

• srikanth_vin 86046

  Newbie

  Points: 8

  More actions

  February 17, 2016 at 12:15 pm

  #1859356

  wow..it worked. Thanks a ton !