0

I'm trying to import part of an xml file into a SQL Server database, but while the query successfully executes there is nothing in the table.

XML:

<?xml version="1.0" encoding="utf-16"?>
<config>
<company>
<Code>0514</Code>
<Name>Company A</Name>
<ContactName>Alan Smith</ContactName>
<ContactTel></ContactTel>
<ContactMail>random@random.net</ContactMail>
<System>type A</System>
</company>
</config>

Then here is the query I run

DECLARE @XML XML = 'C:\Users\Ramdon\file.xml';
INSERT INTO #tmp_Company2 
SELECT
Code = Events.value('@Code', 'char(6)'),
Name = Events.value('@Name', 'varchar(100)'),
ContactName =Events.value('@ContactName', 'varchar(100)'),
ContactTel =Events.value('@ContactTel', 'varchar(15)'),
ContactMail =Events.value('@ContactMail', 'varchar(100)'),
System =Events.value('@System', 'char(20)'),
FROM @XML.nodes('//config/company') as  XTbl(Events);

select * from #tmp_Company2;

Everything I've read suggests it should work but I get nothing.

  • 2
    Your XML isn't well formed. <Code>0514</PracticeCode> should be <Code>0514</Code> and there is a missing <Practice> after company and a missing </company> before </config> – McNets May 11 '18 at 14:55
  • What happens when you run the select without the insert? – Erik Darling May 11 '18 at 15:02
  • @McNets, sorry that was me modifying the original format and missing stuff. – Exostrike May 11 '18 at 15:09
  • @sp_BlitzErik nothing still selects nothing – Exostrike May 11 '18 at 15:09
  • 1
    Yeah, what you're doing now won't load an XML file from the file system. You need to start a few steps back. – Erik Darling May 11 '18 at 15:11
1

As I pointed out your XML file isn't well formed. If you can convert your XML text into this:

declare @XML xml = 
'<?xml version="1.0" encoding="utf-8"?>
 <config>
   <company>
     <Practice>
       <Code>0514</Code>
       <Name>Company A</Name>
       <ContactName>Alan Smith</ContactName>
       <ContactTel></ContactTel>
       <ContactMail>random@random.net</ContactMail>
       <SystemType>type A</SystemType>
     </Practice>
   </company>
 </config>';

Then you could use next query to get data content:

select T2.lin.value('(./Code)[1]', 'char(6)') Code,
       T2.lin.value('(./Name)[1]', 'varchar(100)') Name,
       T2.lin.value('(./ContactName)[1]', 'varchar(100)') ContactName,
       T2.lin.value('(./ContactTel)[1]', 'varchar(15)') ContactTel,
       T2.lin.value('(./ContactMail)[1]', 'varchar(100)') ContactMail,
       T2.lin.value('(./SystemType)[1]', 'varchar(100)') System
       into #tmp_Company2
from   @XML.nodes('/config/company/Practice') T2(lin);
select * from #tmp_Company2;
GO
Code   | Name      | ContactName | ContactTel | ContactMail       | System
:----- | :-------- | :---------- | :--------- | :---------------- | :-----
0514   | Company A | Alan Smith  |            | random@random.net | type A

db<>fiddle here

  • thank you for this (don't have the karma to upvote this). Also ran into a problem with the encoding, needed to change it to utf-8 for some reason. – Exostrike May 11 '18 at 15:28
  • You can use utf-16 if you convert your xml file to NVARCHAR. Have a look: dbfiddle.uk/… – McNets May 11 '18 at 15:32
  • yeah discovered that when it flashed up the error. Now I just need to work out how to do it to a file rather than text. – Exostrike May 11 '18 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.