September 1, 2016 at 10:53 am
Hi,
I'm stuck on trying to query the attached xml file. So far I have managed to read the file and show the xml in a select.
But I don´t really know how to extract the data... Can anyone help with this task?
Many thanks,
Note: I have rename the extension of the file to txt so that I can attach it to this post.
CREATE DATABASE OPENXMLTesting
GO
USE OPENXMLTesting
GO
CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)
INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK 'C:\REPORT.xml', SINGLE_BLOB) AS x;
USE OPENXMLTesting
GO
September 1, 2016 at 8:01 pm
This should get you to the starting node:
with xmlnamespaces ('urn:schemas-microsoft-com:xml-msdata' as m,
'urn:schemas-microsoft-com:xml-diffgram-v1' as d)
select c.value('local-name(.)','varchar(100)') as datanodename
from XMLwithOpenXML
cross apply xmldata.nodes('//d:diffgram/ReportData') a(b)
cross apply b.nodes('*') b(c)
You should google SQLXML and read up a bit - this is not a beginner XML file to interact with, so it might take some homework to understand it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 1, 2016 at 10:54 pm
Many thanks for pointing me in the right direction.
I will do some reading as suggested
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply