Link to home
Start Free TrialLog in
Avatar of Danny Kon
Danny KonFlag for Netherlands

asked on

Help needed with Powershell XML to MySQL

How to Connect to MySQL server read an XML file and send the data to MySQL table
<customer>
<newcustomer>true</newcustomer>
<firstname>Test</firstname>
<lastname>Name</lastname>
</customer>

DB Test
Table customers
NAME

Thanks for helping

Danny
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi Danny,
Change your XML like below , you can add more nodes if you want.. save the file as Pawan.XML on c:\ drive

--

<?xml version="1.0" encoding="utf-8" ?>
<customer>
<newcustomer>true</newcustomer>
<firstname>Test</firstname>
<lastname>Name</lastname>
</customer>
<customer>
<newcustomer>true</newcustomer>
<firstname>Test1</firstname>
<lastname>Name2</lastname>
</customer>
--

Open in new window


Please try below - create table and run the Load command.

--

CREATE TABLE Customerx
(
	newcustomer varchar(255),
	firstname varchar(255),
	lastname varchar(255),
);

LOAD XML LOCAL INFILE "C:\\Pawan.xml"
INTO TABLE Customerx
ROWS IDENTIFIED BY '<customer>';

--

Open in new window


Ref more at - https://dev.mysql.com/doc/refman/5.5/en/load-xml.html

Hope it helps!
Avatar of Danny Kon

ASKER

Pawan Kumar,

Thanks for your answer, but i need to do this external with powershell, I have to send the data to an existing table not create a table.

Danny
Hi Danny,
I am not sure whether we can directly insert the data in the table using power shell. I don't think it is available. Is it?

If you already have table then we can use below-

--
LOAD XML LOCAL INFILE "C:\\Pawan.xml"
INTO TABLE Customerx
ROWS IDENTIFIED BY '<customer>';
--

Open in new window


Hope it helps!
ASKER CERTIFIED SOLUTION
Avatar of oBdA
oBdA

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This was exactly i was looking for

In one time 100% correct :)

Thanks Danny