Read xml file into DataSet when xmlns' are present

Question

The end goal is to be able to query an XML file that has been provided by a third party. I need to be able to query each element to return its results. I am not stuck on the idea of reading it into a dataset, however, for me it just seems to be the easiest solution.

Input XML Full XML On GitHub

My XML File has 14 xmlns's and a xsi, when I attempted to read the XML File into a dataset:

DataSet myset = new DataSet();
myset.ReadXml(_fileName); 

I get an error message: System.Data.DuplicateNameException: 'Cannot add a column named 'IncidentAugmentation': a nested table with the same name already belongs to this DataTable.'

It is obvious that the "IncidentAugmentation" element already exist in the XML, however, it is in a different xmlns.

Using XDocument to query the XML, I want to search all reports where the "IncidentAugmentation" value = "N", but when I run the following results, I get 0 returns, and two returns are expected.

XDocument submissions = XDocument.Load(_fileName);

var allReportsWithClearCodeN = submissions.Elements("IncidentAugmentation")
    .Where(m => m.Element("IncidentExceptionalClearanceCode").Value == "N");

While I am not positive if the xmlns's are the issue, that is where I have been focusing my attention.

How am I able to better import that XML File into a DataTable or a quarriable format? or am I just way off in my methodology?

Source Code on GitHub

Answer 1

I wanted to post the answer given to me by Yizhak because for me, finding the answer was extremely difficult.

The issue I was having is getting an XML File into a format that I could query. Turns out that the easiest way to do this is through Microsoft SQL Server Management Studio.

Prerequisite

You must have ADMINISTER BULK OPERATIONS or be a member of the fixed server role, bulkadmin, on the SQL Server. Also, the XML File MUST be on a network share or directly on the SQL Server.

Procedure

Login to SSMS and open a new Query Window and enter the following:

;WITH XMLNAMESPACES (   DEFAULT 'http://fbi.gov/cjis/nibrs/4.2', 
                        'http://release.niem.gov/niem/structures/3.0/' as s,
                        'http://fbi.gov/cjis/1.0' as cjis,
                        'http://release.niem.gov/niem/niem-core/3.0/' as nc,
                        'http://release.niem.gov/niem/domains/jxdm/5.2/' as j,
                        'http://www.beyond2020.com/tnibrs/2.1' as tnibrs
                    ) 
, rs (xmlData) AS
(
    SELECT TRY_CAST(BulkColumn AS XML)
    FROM OPENROWSET(BULK N'C:\\NETWORK_SHARE_PATH\nibrs_GroupAIncident_Sample.xml', SINGLE_BLOB) AS x
)
--INSERT INTO targetTable (...)
SELECT  c.value('@s:id', 'VARCHAR(100)') AS ID
, c.value('(ReportHeader/NIBRSReportCategoryCode/text())[1]', 'VARCHAR(50)') AS NIBRSReportCategoryCode
, c.value('(ReportHeader/ReportActionCategoryCode/text())[1]', 'VARCHAR(5)') AS ReportActionCategoryCode
, c.value('(ReportHeader/ReportDate/nc:YearMonthDate/text())[1]', 'VARCHAR(10)') AS YearMonthDate
, c.value('(ReportHeader/ReportingAgency/j:OrganizationAugmentation/j:OrganizationORIIdentification/nc:IdentificationID/text())[1]', 'VARCHAR(10)') AS IdentificationID


, c.value('(nc:Incident/nc:ActivityIdentification/nc:IdentificationID/text())[1]', 'VARCHAR(10)') AS IdentificationID
, c.value('(nc:Incident/nc:ActivityDate/nc:DateTime/text())[1]', 'VARCHAR(MAX)') AS DateTime
, c.value('(nc:Incident/nc:ActivityDate/nc:YearMonthDate/text())[1]', 'VARCHAR(MAX)') AS YearMonthDate
, c.value('(nc:Incident/nc:ActivityDate/nc:Date/text())[1]', 'VARCHAR(MAX)') AS Date

--Incident
, c.value('(nc:Incident/j:IncidentAugmentation/j:IncidentExceptionalClearanceCode/text())[1]', 'VARCHAR(MAX)') AS IncidentExceptionalClearanceCode
, c.value('(nc:Incident/tnibrs:IncidentLocation/tnibrs:Coordinates/tnibrs:Latitude/text())[1]', 'VARCHAR(MAX)') AS Latitude
, c.value('(nc:Incident/tnibrs:IncidentLocation/tnibrs:Coordinates/tnibrs:Longitude/text())[1]', 'VARCHAR(MAX)') AS Longitude
, c.value('(nc:Incident/tnibrs:IncidentLocation/tnibrs:Address/tnibrs:StreetNumber/text())[1]', 'VARCHAR(MAX)') AS StreetNumber

, c.value('(nc:Incident/tnibrs:IncidentLocation/tnibrs:Address/tnibrs:StreetNumber/text())[1]', 'VARCHAR(MAX)') AS StreetNumber
, c.value('(nc:Incident/tnibrs:IncidentLocation/tnibrs:Address/tnibrs:StreetName/text())[1]', 'VARCHAR(MAX)') AS StreetName
, c.value('(nc:Incident/tnibrs:IncidentLocation/tnibrs:Address/tnibrs:StreetNameExtension/text())[1]', 'VARCHAR(MAX)') AS StreetNameExtension
, c.value('(nc:Incident/tnibrs:IncidentLocation/tnibrs:Address/tnibrs:CityName/text())[1]', 'VARCHAR(MAX)') AS CityName
, c.value('(nc:Incident/tnibrs:IncidentLocation/tnibrs:Address/tnibrs:StateCode/text())[1]', 'VARCHAR(MAX)') AS StateCode
, c.value('(nc:Incident/tnibrs:IncidentLocation/tnibrs:Address/tnibrs:ZipCode/text())[1]', 'VARCHAR(MAX)') AS ZipCode


--Offense
 --,c.value('j:Offense/@s:id[2]', 'VARCHAR(100)') AS OffenseID
 ,c.value('(j:Offense/tnibrs:OffenseUCRCode/text())[1]', 'VARCHAR(MAX)') AS OffenseUCRCode
 ,c.value('(j:Offense/tnibrs:OffenseUCRCode/text())[1]', 'VARCHAR(MAX)') AS OffenseUCRCode


FROM rs 
CROSS APPLY xmlData.nodes('/Submission/Report') AS t(c)

Again, I want to thank Yizhak for helping me with this.