XML Openrowset column Issue

• gjoelson 29755

  SSC Eights!

  Points: 848

  More actions

  January 19, 2021 at 3:12 pm

  #3835079

  Morning ,

  I'm having an issue returning any records from an xml file,  using an Openrowset query. My purpose is to insert the data from the xml file into a table.

  When I run the openrowset select portion of the query, I can get the xml data to return in xml format, so I know its reading the file correctly , but when I add the select to get the data in table column format the Query returns blank.

  Could you advise what I'm missing here , I've looked at the syntax and looked at other examples and I'm not sure where the issue is.

  Thank you

  Select 
  A.REPORT.query('week_date').value('.','nvarchar(50)') as week_date,
  A.REPORT.query('well_count').value('.','numeric') as well_count,
  A.REPORT.query('frac_operator').value('.','varchar(50)') as frac_operator,
  A.REPORT.query('pressure_pumper').value('.','varchar(50)') as pressure_pumper,
  A.REPORT.query('shale_basin').value('.','varchar(50)') as shale_basin,
  A.REPORT.query('state_county').value('.','varchar(50)') as state_county,
  A.REPORT.query('crews_count').value('.','numeric') as crews_count,
  A.REPORT.query('county_name').value('.','varchar(50)') as county_name,
  A.REPORT.query('state_code').value('.','varchar(50)') as state_code
  
  from
  (
  Select cast(c as xml) From openrowset(BULK 'C:\temp\testreport.xml',SINGLE_BLOB) as T(c) 
  )as S(c)
  cross apply c.nodes('REPORTS/REPORT') as A(REPORT)
  

   

   

   

  Attachments:

  You must be logged in to view attached files.

• frederico_fonseca

  SSCoach

  Points: 16234

  More actions

  January 19, 2021 at 4:27 pm

  #3835107

  because your node names is not reports/report but RECORDS/RECORD

  cross apply c.nodes('/RECORDS/RECORD') as A(REPORT)

• gjoelson 29755

  SSC Eights!

  Points: 848

  More actions

  January 19, 2021 at 9:02 pm

  #3835207

  Unreal !!

  I wont tell you how much time I spent on this.....double checked everything, but didn't see that -  Couldn't see the wood for the trees.

  Thank you so much.