Updating xml with php

Behind this amazing feat of human cleverness is the coming together of a few technologies collectively known as AJAX.

In this lesson you will learn: I recommend you get access to a web server to carry out the example code illustrated in this lesson.

In this section, I will walk through the different processes within the Insert stored procedure, which are to extract the Node Path and Table names from the XML, retrieve the schema datatypes, primary keys for that table, prepare the statement and then lastly loop through the XML to perform the request. The following breaks apart the INSERT stored procedure and I'll explain each step in the process. -- ============================================= -- Author: WA -- Create date: 12/24/2009 -- Description: Translate XML to SQL Record Set for INSERT -- Dependencies: [dbo].[fn Get Table Schema Insert], [dbo].[fn Set Table Schema Select], [dbo].[fn Get Table Schema Select Into],[dbo].[fn Is Date] -- ============================================= CREATE PROCEDURE [dbo].[pr XMLData Insert] ( @Xml Data XML ) AS BEGIN SET NOCOUNT ON SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET NUMERIC_ROUNDABORT OFF DECLARE @hdoc INT -- Prepare XML document EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml Data After testing several XML methods, the quickest way I found was to use the sp_xml_preparedocument then insert the results into a temporary dataset in order to quickly query values from the XML to process.

updating xml with php-59updating xml with php-90

The following production code is what I used for inserting and updating database tables using XML as the input.

These scripts are for processing data for any table to insert or update data.

Basically, the correctly formatted XML is generated with the table name (authors) as the parent node, rows are delimited by Note: XML is case-sensitive therefore whatever you have for table names and column names must exactly match the XML.

For those wondering how this is done auto-magically, this select query will generate a sample XML document from the authors table in the pubs database: Now that we know what we are working with, we can move onto processing the transaction.

I have two processes one for inserting data and another for updating data.

I will walk through the Insert and Update processes, so you have a better understanding of how the stored procedures work.

When we have a datetime value to process, a value of NULL is translated to 1900-01-01 .000, which is why I created the fn Is Date() function to turn it back to NULL.

After you have created all of these objects in your database (in our case the pubs database) we can then use them to create new records or update existing records.

The support functions provided, retrieve the table schema with their data types, functions to deal with XML dates, primary keys of the table and what fields can be updated.

The following article breaks down this process from beginning to end.

A few points of interest, I used a while loop verses a cursor to keep down the resource overhead and keep the speed up, and for those that haven't learned the hard way with sp_executesql since we have to pass in the XML into the statement, we only have nvarchar(4000) to store the statement, which causes chaos when you have tables with many columns.

Tags: , ,