question

sql83den avatar image
sql83den asked

How to load multiple XML files (with namespace) in SQL Server tables?

Hi,

What would be the best way to load some complex XML files in SQL Server tables? Each file is over 1K lines of code.

Thanks in advance.

My xml file structure looks like below:

<?xml version="1.0" encoding="UTF-8"?> <ABCDataSet xmlns="http://www.w3.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.w3.org http://www.w3.org/media/file_v3/release-3.4/XSDs/ABCDataSet_v3.xsd"><!-- This 3 file was generated from aan application le via an XML Stylesheet Language Transformation (XSLT). --> <Header> <DemographicGroup> <Location.01>474</Location.01> <Location.02>474</Location.02> <Location.04>08</Location.04> </DemographicGroup> <ConfigDetails> <ConfigDetails.CustomGroup CustomElementID="5303300"> <ConfigDetails.01>XYZCSS_OnsetDateTime</ConfigDetails.01> <ConfigDetails.02>The onset time.</ConfigDetails.02> <ConfigDetails.03>9902003</ConfigDetails.03> <ConfigDetails.04>9923001</ConfigDetails.04> <ConfigDetails.05>9903007</ConfigDetails.05> </ConfigDetails.CustomGroup> <ConfigDetails.CustomGroup CustomElementID="5303301"> <ConfigDetails.01>XYZCSS_FD</ConfigDetails.01> <ConfigDetails.02>CSS FD value</ConfigDetails.02> <ConfigDetails.03>9902009</ConfigDetails.03> <ConfigDetails.04>9923001</ConfigDetails.04> <ConfigDetails.05>9903007</ConfigDetails.05> <ConfigDetails.06>Normal</ConfigDetails.06> <ConfigDetails.06>Abnormal</ConfigDetails.06> </ConfigDetails.CustomGroup> </ConfigDetails> <CustomerCare> <Cust_Entry> <Cust_Entry.01>9b09f0bd84b24f858073abb30099s55</Cust_Entry.01> <Cust_Entry.SoftwareApplicationGroup> <Cust_Entry.02>XYZ Solutions</Cust_Entry.02> <Cust_Entry.03>XYZ App</Cust_Entry.03> <Cust_Entry.04>5.0</Cust_Entry.04> </Cust_Entry.SoftwareApplicationGroup> </Cust_Entry> <Cust_Response> <Cust_Response.AgencyGroup> <Cust_Response.01>474</Cust_Response.01> <Cust_Response.02>ABC Corp</Cust_Response.02> </Cust_Response.AgencyGroup> <Cust_Response.03>20-044818</Cust_Response.03> <Cust_Response.04>20-044818</Cust_Response.04> <Cust_Response.ServiceGroup> <Cust_Response.05>2205001</Cust_Response.05> </Cust_Response.ServiceGroup> <Cust_Response.07>2207007</Cust_Response.07> <Cust_Response.08>2208013</Cust_Response.08> <Cust_Response.09>2209011</Cust_Response.09> <Cust_Response.10 xsi:nil="true" NV="7701003"/> <Cust_Response.11 xsi:nil="true" NV="7701003"/> <Cust_Response.12 xsi:nil="true" NV="7701003"/> <Cust_Response.13>T19</Cust_Response.13> <Cust_Response.14>T19</Cust_Response.14> <Cust_Response.15>2215003</Cust_Response.15> <Cust_Response.23>2223001</Cust_Response.23> <Cust_Response.24>2224015</Cust_Response.24> </Cust_Response> </CustomerCare> </Header> </ABCDataSet>

ssissql-serverxml
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·

If you could provide an example of how you would like the output, from this example input, then we can help get you started with an example query that will extract the data

0 Likes 0 ·

3 Answers

·
Jon Crawford avatar image
Jon Crawford answered

Shred it and load into normalized tables/columns

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sql83den avatar image
sql83den answered

I am new to XML and found some xml parsing examples online however only for simple XML files. My XML file has multiple levels with namespace. Any code logic on shredding this one would be very helpful.

Thanks

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jon Crawford avatar image
Jon Crawford answered

Check out the Stairway to XML : https://www.sqlservercentral.com/stairways/stairway-to-xml

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.