I'm trying to write a stored procedure that will parse XML attributes and populate columns within a DB with the stripped data. I'm a complete novice who prior to this week knew nothing about SQL commands, I've tried to bring myself up to speed at least enough to be dangerous. My understanding at least is that I need to perform a bulk insert. I'm hoping someone might be able to use the example XML code and table/column information below and provide some input.
Example XML file:
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE Asset_Collection SYSTEM "Asset_Collection.dtd">
Description="Random XML Feed Test"
<Asset Asset_Type="Lost my assets at the Casino">
<App_Data Name="ProdID" Value="12345" />
<App_Data Name="CustomerID" Value="Joe Blow" />
<App_Data Name="AreaCode" Value="708" />
Table/Columns which need to be inserting into:
I appreciate the reply, the procedure posted works but I was wondering if there's a way to avoid having to list all values in the Stored Procedure. The example XML I posted only contains ProdID, CustomerID and AreaCode, the actual XML used in my environment can have hundreds of lines of data. If there's a way to pull all the data in quotes and create two columns without having to specify each attribute verbatim, please let me know. Also the values will change periodically so I need to refrain from hard coding them into the procedure. If there's anything I'm unclear on, let me know...I'll do my best to provide the proper context.
I should also add that I'm wanting the stored procedure to pull the data from a file located at \\<servername>\<pathto>\file.xml. So hopefully if I can pull using the same stored procedure that would be great.