January 19, 2021 at 3:12 pm
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)
January 19, 2021 at 4:27 pm
because your node names is not reports/report but RECORDS/RECORD
cross apply c.nodes('/RECORDS/RECORD') as A(REPORT)
January 19, 2021 at 9:02 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply