asked on

XML to SQL Database

I need to load an SQL Server data base from a XML file. I have the code working except when the XML group has a different value.
Example: If my query is this:

INSERT INTO ImplantDeviceDB (publicDeviceRecordKey,deviceId,versionModelNumber,companyName, deviceDescription, serialNumber, productCodeName,deviceSterile)
SELECT
   MY_XML.Customer.query('publicDeviceRecordKey').value('.', 'VARCHAR(50)'),

    MY_XML.Customer.query('identifiers/identifier/deviceId').value('.', 'VARCHAR(150)'),
   
   MY_XML.Customer.query('versionModelNumber').value('.', 'VARCHAR(300)'),

   MY_XML.Customer.query('companyName').value('.', 'VARCHAR(300)'),

 MY_XML.Customer.query('deviceDescription').value('.', 'VARCHAR(300)'),

 MY_XML.Customer.query('serialNumber').value('.', 'VARCHAR(50)'),

 
     MY_XML.Customer.query('productCodes/fdaProductCode/productCodeName').value('.', 'VARCHAR(50)'),
     MY_XML.Customer.query('sterilization/deviceSterile').value('.', 'VARCHAR(50)')
   
FROM (SELECT CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK 'C:\testing.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      CROSS APPLY MY_XML.nodes('device') AS MY_XML (Customer);

Open in new window

It works great with this XML Loop

<device>
  <publicDeviceRecordKey>3a391ef0-1a4c-40bb-98c1-1bbf8d032161</publicDeviceRecordKey>

    <publicVersionNumber>4</publicVersionNumber>
 
  <identifiers>
    <identifier>
      <deviceId>M75240326ROSR11</deviceId>
    
    </identifier>
  </identifiers>

  <versionModelNumber>40326ROS </versionModelNumber>

  <companyName>STRADIS MEDICAL, LLC</companyName>
 
  <deviceDescription>CUSTOM DENTAL KIT SURGICAL PROCEDURE PACK </deviceDescription>

  <serialNumber>false</serialNumber>

  <productCodes>
    <fdaProductCode>
    
      <productCodeName>Unit, Operative Dental</productCodeName>
    </fdaProductCode>
  </productCodes>

  <sterilization>
    <deviceSterile>true</deviceSterile>
 
  </sterilization>
</device>

Open in new window

But some of the XML Loops have an added section of

  <gmdn>
      <gmdnPTName>General/plastic surgical procedure kit, non-medicated, single-use</gmdnPTName>
     
    </gmdn>

Open in new window

I really only need 3 of the items (deviceID, companyName, and productCodeName) out of any of them and all loops have those three items. Is there a way to change my code to just those 3 items and loop through everything? I have never done this before so am stuck.

ste5an

Please post an excise, properly formatted sample of your XML file showing what you mean. Cause there is no such thing like a XML loop..

katlees

ASKER

Sorry, by loop I mean I have an xml file with 100's of <devices> Like:

<device>
  <publicDeviceRecordKey>3a391ef0-1a4c-</publicDeviceRecordKey>

    <publicVersionNumber>41</publicVersionNumber>
 
  <identifiers>
    <identifier>
      <deviceId>M26ROSR11</deviceId>
        </identifier>
  </identifiers>

  <versionModelNumber>40326ROS123 </versionModelNumber>

  <companyName>STRADIS MEDICAL, LLC</companyName>
 
  <deviceDescription>CUSTOM DENTAL KIT SURGICAL </deviceDescription>

  <serialNumber>false</serialNumber>

  <productCodes>
    <fdaProductCode>
          <productCodeName>Operative Dental</productCodeName>
    </fdaProductCode>
  </productCodes>

  <sterilization>
    <deviceSterile>true</deviceSterile>
 
  </sterilization>
</device>
<device>
  <publicDeviceRecordKey>3a391ef0-98c1-1bbf8d032161</publicDeviceRecordKey>

    <publicVersionNumber>6</publicVersionNumber>
 
  <identifiers>
    <identifier>
      <deviceId>M75240OSR11</deviceId>
        </identifier>
  </identifiers>

  <versionModelNumber>40326ROS123456 </versionModelNumber>

  <companyName>STRADIS MEDICAL, LLC</companyName>
 
  <deviceDescription>CUSTOM DENTAL KIT SURGICAL PROCEDURE PACK </deviceDescription>

  <serialNumber>false</serialNumber>

  <productCodes>
    <fdaProductCode>
          <productCodeName>Unit, Operative Dental</productCodeName>
    </fdaProductCode>
  </productCodes>
<gmdn>
      <gmdnPTName>General/plastic surgical procedure kit, non-medicated, single-use</gmdnPTName>
     
    </gmdn>

  <sterilization>
    <deviceSterile>true</deviceSterile>
 
  </sterilization>
</device>

<device>
  <publicDeviceRecordKey>3a391ef0-1a4c-40bb-98c1-1bbf8d032161</publicDeviceRecordKey>

    <publicVersionNumber>4</publicVersionNumber>
 
  <identifiers>
    <identifier>
      <deviceId>M75240326ROSR11</deviceId>
        </identifier>
  </identifiers>

  <versionModelNumber>40326ROS </versionModelNumber>

  <companyName>STRADIS MEDICAL, LLC</companyName>
 
  <deviceDescription>CUSTOM DENTAL KIT SURGICAL PROCEDURE PACK </deviceDescription>

  <serialNumber>false</serialNumber>

  <productCodes>
    <fdaProductCode>
          <productCodeName>Unit, Operative Dental</productCodeName>
    </fdaProductCode>
  </productCodes>

  <sterilization>
    <deviceSterile>true</deviceSterile>
 
  </sterilization>
</device>

Open in new window

ste5an

It's still invalid XML. When corrected, then it is probably:

DECLARE @Xml XML = N'
<root>
	<device>
		<publicDeviceRecordKey>3a391ef0-1a4c-</publicDeviceRecordKey>
		<publicVersionNumber>41</publicVersionNumber>
		<identifiers>
			<identifier>
				<deviceId>M26ROSR11</deviceId>
			</identifier>
		</identifiers>
		<versionModelNumber>40326ROS123 </versionModelNumber>
		<companyName>STRADIS MEDICAL, LLC</companyName>
		<deviceDescription>CUSTOM DENTAL KIT SURGICAL </deviceDescription>
		<serialNumber>false</serialNumber>
		<productCodes>
			<fdaProductCode>
				<productCodeName>Operative Dental</productCodeName>
			</fdaProductCode>
		</productCodes>
		<sterilization>
			<deviceSterile>true</deviceSterile>
		</sterilization>
	</device>
	<device>
		<publicDeviceRecordKey>3a391ef0-98c1-1bbf8d032161</publicDeviceRecordKey>
		<publicVersionNumber>6</publicVersionNumber>
		<identifiers>
			<identifier>
				<deviceId>M75240OSR11</deviceId>
			</identifier>
		</identifiers>
		<versionModelNumber>40326ROS123456 </versionModelNumber>
		<companyName>STRADIS MEDICAL, LLC</companyName>
		<deviceDescription>CUSTOM DENTAL KIT SURGICAL PROCEDURE PACK </deviceDescription>
		<serialNumber>false</serialNumber>
		<productCodes>
			<fdaProductCode>
				<productCodeName>Unit, Operative Dental</productCodeName>
			</fdaProductCode>
		</productCodes>
		<gmdn>
			<gmdnPTName>General/plastic surgical procedure kit, non-medicated, single-use</gmdnPTName>
		</gmdn>
		<sterilization>
			<deviceSterile>true</deviceSterile>
		</sterilization>
	</device>
	<device>
		<publicDeviceRecordKey>3a391ef0-1a4c-40bb-98c1-1bbf8d032161</publicDeviceRecordKey>
		<publicVersionNumber>4</publicVersionNumber>
		<identifiers>
			<identifier>
				<deviceId>M75240326ROSR11</deviceId>
			</identifier>
		</identifiers>
		<versionModelNumber>40326ROS </versionModelNumber>
		<companyName>STRADIS MEDICAL, LLC</companyName>
		<deviceDescription>CUSTOM DENTAL KIT SURGICAL PROCEDURE PACK </deviceDescription>
		<serialNumber>false</serialNumber>
		<productCodes>
			<fdaProductCode>
				<productCodeName>Unit, Operative Dental</productCodeName>
			</fdaProductCode>
		</productCodes>
		<sterilization>
			<deviceSterile>true</deviceSterile>
		</sterilization>
	</device>
</root>';

SELECT	A.Device.value('publicDeviceRecordKey[1]', 'NVARCHAR(255)'),
		A.Device.value('identifiers[1]/identifier[1]/deviceId[1]', 'NVARCHAR(255)'),   
		A.Device.value('versionModelNumber[1]', 'NVARCHAR(MAX)'),
		A.Device.value('companyName[1]', 'NVARCHAR(MAX)'),
		A.Device.value('deviceDescription[1]', 'NVARCHAR(MAX)'),
		A.Device.value('serialNumber[1]', 'NVARCHAR(255)'), 
		A.Device.value('productCodes[1]/fdaProductCode[1]/productCodeName[1]', 'NVARCHAR(255)'),
		A.Device.value('sterilization[1]/deviceSterile[1]', 'NVARCHAR(255)') ,
		A.Device.value('gmdn[1]/gmdnPTName[1]', 'NVARCHAR(255)')
FROM @Xml.nodes('/root/device') AS A ( Device );

Open in new window

katlees

ASKER

ste5an, I tried to put in just the part of the xml code that I was having trouble with. yes, there are other lines at the top.

My problem is I have an xml file with 1000 of these on there. There is no way for me to go through and see which ones have a unique element like the <gmdn> in the second example.

Is there a way to pull just a few of the values into the sql table and ignore all the rest. Say I just want deviceID, companyName, and productCodeName?

I tried to take everything else out of the XML and it won't load my database.

I apologize if I am using the wrong terminology. I have never used XML before so am trying to research and attempt different things.

ste5an

It's sure possible. But your need to explain more clearly what you want. Post a concise and complete XML. Concise is the keyword here. And describe the output for it.

katlees

ASKER

Thanks for your patients ste5an,

Here is a very shortened version of my xml file. Exactly how it is.

Is there a way to upload to a sqlserver database ONLY these five elements:
<publicVersionNumber>
<deviceId>
<verrsionModelNumber>
<companyName>
<deviceDescription>

If I were to go through and remove all other elements in my file to just these 5 elements, and use the code below, it will load. But I have thousands I would have to go through so would like a way to just pick out the 5 elements without having to edit the file.

INSERT INTO ImplantDeviceDB (publicVersionNumber, deviceId, versionModelNumber, companyName, deviceDescription)
SELECT
       
 MY_XML.Customer.query('publicVersionNUmber').value('.', 'VARCHAR(50)'),
    testing.xmlMY_XML.Customer.query('identifiers/identifier/deviceId').value('.', 'VARCHAR(150)'),
       
MY_XML.Customer.query('versionModelNumber').value('.', 'VARCHAR(300)'),
 
MY_XML.Customer.query('companyName').value('.', 'VARCHAR(300)'),
 
MY_XML.Customer.query('deviceDescription').value('.', 'VARCHAR(300)')
 
FROM (SELECT CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK 'C:\testing.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      CROSS APPLY MY_XML.nodes('device') AS MY_XML (Customer);

Open in new window

katlees

ASKER

Dang it, I'm new at this. The file uploaded in the middle of the code.
testing.xml

ste5an

An XML document or fragment has always a fixed structure. This fixed structure for addressing elements is called path. Using XPath expressions to address them.

Without knowing the XML and its structure its is not possible to get this information.
Well it is, but it means that you'll may get the wrong data.

Based on the above sample using a correct XML document, it is:

DECLARE @Xml XML = N'
<root>
	<device>
		<publicDeviceRecordKey>3a391ef0-1a4c-</publicDeviceRecordKey>
		<publicVersionNumber>41</publicVersionNumber>
		<identifiers>
			<identifier>
				<deviceId>M26ROSR11</deviceId>
			</identifier>
		</identifiers>
		<versionModelNumber>40326ROS123 </versionModelNumber>
		<companyName>STRADIS MEDICAL, LLC</companyName>
		<deviceDescription>CUSTOM DENTAL KIT SURGICAL </deviceDescription>
		<serialNumber>false</serialNumber>
		<productCodes>
			<fdaProductCode>
				<productCodeName>Operative Dental</productCodeName>
			</fdaProductCode>
		</productCodes>
		<sterilization>
			<deviceSterile>true</deviceSterile>
		</sterilization>
	</device>
	<device>
		<publicDeviceRecordKey>3a391ef0-98c1-1bbf8d032161</publicDeviceRecordKey>
		<publicVersionNumber>6</publicVersionNumber>
		<identifiers>
			<identifier>
				<deviceId>M75240OSR11</deviceId>
			</identifier>
		</identifiers>
		<versionModelNumber>40326ROS123456 </versionModelNumber>
		<companyName>STRADIS MEDICAL, LLC</companyName>
		<deviceDescription>CUSTOM DENTAL KIT SURGICAL PROCEDURE PACK </deviceDescription>
		<serialNumber>false</serialNumber>
		<productCodes>
			<fdaProductCode>
				<productCodeName>Unit, Operative Dental</productCodeName>
			</fdaProductCode>
		</productCodes>
		<gmdn>
			<gmdnPTName>General/plastic surgical procedure kit, non-medicated, single-use</gmdnPTName>
		</gmdn>
		<sterilization>
			<deviceSterile>true</deviceSterile>
		</sterilization>
	</device>
	<device>
		<publicDeviceRecordKey>3a391ef0-1a4c-40bb-98c1-1bbf8d032161</publicDeviceRecordKey>
		<publicVersionNumber>4</publicVersionNumber>
		<identifiers>
			<identifier>
				<deviceId>M75240326ROSR11</deviceId>
			</identifier>
		</identifiers>
		<versionModelNumber>40326ROS </versionModelNumber>
		<companyName>STRADIS MEDICAL, LLC</companyName>
		<deviceDescription>CUSTOM DENTAL KIT SURGICAL PROCEDURE PACK </deviceDescription>
		<serialNumber>false</serialNumber>
		<productCodes>
			<fdaProductCode>
				<productCodeName>Unit, Operative Dental</productCodeName>
			</fdaProductCode>
		</productCodes>
		<sterilization>
			<deviceSterile>true</deviceSterile>
		</sterilization>
	</device>
</root>';

SELECT	A.Device.value('publicVersionNumber[1]', 'NVARCHAR(255)') AS publicVersionNumber,
		A.Device.value('identifiers[1]/identifier[1]/deviceId[1]', 'NVARCHAR(255)') AS deviceId,
		A.Device.value('versionModelNumber[1]', 'NVARCHAR(255)') AS versionModelNumber,
		A.Device.value('companyName[1]', 'NVARCHAR(255)') AS companyName,
		A.Device.value('deviceDescription[1]', 'NVARCHAR(255)') AS deviceDescription
FROM @Xml.nodes('/root/device') AS A ( Device );

Open in new window

When trying to catch all, which already said, may return the wrong nodes data:

SELECT	A.Device.value('publicVersionNumber[1]', 'NVARCHAR(255)') AS publicVersionNumber,
		A.Device.value('identifiers[1]/identifier[1]/deviceId[1]', 'NVARCHAR(255)') AS deviceId,
		A.Device.value('versionModelNumber[1]', 'NVARCHAR(255)') AS versionModelNumber,
		A.Device.value('companyName[1]', 'NVARCHAR(255)') AS companyName,
		A.Device.value('deviceDescription[1]', 'NVARCHAR(255)') AS deviceDescription
FROM @Xml.nodes('//device') AS A ( Device );

Open in new window

This catch-all approach is also significantly slower on larger fragments or documents.

CAVEAT: Your file is not a proper, valid XML document.

The difference is the XPath for the nodes() method. '/root/device' means an exact path starting at the root node 'root' and selecting all direct descendants only named 'device'. The XPath '//device' is short-hand for '/*/device' which means select any node named 'device' and don't care on what level it is found:

DECLARE @Xml XML = N'
<root>
	<device>1</device>
	<someElement>
		<device>2</device>
	</someElement>
</root>
';

SELECT A.Device.value('.', 'INT') AS devicContent ,
       A.Device.value('local-name(..)', 'NVARCHAR(255)') AS deviceParent
FROM   @Xml.nodes('/root/device') A(Device);

SELECT A.Device.value('.', 'INT') AS devicContent ,
       A.Device.value('local-name(..)', 'NVARCHAR(255)') AS deviceParent
FROM   @Xml.nodes('//device') A(Device);

Open in new window

katlees

ASKER

One of the files I was sent is here: I'm hoping this is considered a valid xml file.
There are 114 of these and some over 100,000kb in size
FULLDownload_Part114_Of_114_2021-02-01.xml

I pulled out a few pieces to test with code, that was the file I sent earlier that worked with my code if all the elements/paths matched.

I need to upload to the ImplantDB on our server and have a row for each <device> but only for those 5 pieces of it.

As I said, I have never worked with XML before so appreciate the explanations. I have a folder in my c: drive housing all 114 xml files.

Again, thanks for trying to help me. I feel completely out of my element here.

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

katlees

ASKER

THANK YOU SO MUCH!!! That worked perfectly. I appreciate all of your help.

ste5an

You're welcome.