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

Please add to you question XML sample. And explain what you need out of it, i.e. desired output. There is no need in any `Dataset()`. LINQ to XML is your friend. — Yitzhak Khabinsky, Jun 02 '22 at 18:36
@YitzhakKhabinsky, thank you for your response. I did add my Source Code, including XML on Git — Talsiter, Jun 02 '22 at 22:10
You need to update your original question with a desired output. — Yitzhak Khabinsky, Jun 02 '22 at 22:49
@YitzhakKhabinsky, you are correct... I did update my question with the desired output, but I no longer see it. The results will ultimately be populated into a WinForm based on the needs, as an example, get the MessageDateTime and determine the number of reports submitted for that date, then I would populate the ID of each message to get the contents of the Report node. Also need to query all of the Reports and return records where ReportActionCategoryCode equal to "I". This is why I felt a dataset may be a best option., LINQ may be better but, I don't know how to get it to that point. — Talsiter, Jun 03 '22 at 02:14

score 0 · Answer 1 · answered Jun 09 '22 at 13:52

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.

Read xml file into DataSet when xmlns' are present

1 Answers1