regarding ajax

rajapcs — Thu, 05 Mar 2009 08:05:15 GMT

i am using ajaxany one knoe how to get the xml response using ajax

Making Dynamic Queries Static

Leon Platt — Sun, 28 Apr 2002 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lplatt/makingdynamicqueriesstatic.asp

ASP - Reaping the benifits of SQL 2000's FOR XML clause.

Leon Platt — Sun, 04 Nov 2001 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lplatt/article4_1.asp

RE:

sholliday — Fri, 15 Apr 2005 09:21:00 GMT

This was an excellent "quick tutorial" on XML.

I would add, that there is something you can do with all that XML too.

Especially since this is the "sqlserver"central website.

Here is an example using the code supplied:

if exists (select * from sysobjects where id = object_id('Content')) DROP TABLE Content

if exists (select * from sysobjects where id = object_id('Cup')) DROP TABLE Cup

CREATE TABLE dbo.Cup (

cupid int IDENTITY (1,1) PRIMARY KEY NONCLUSTERED, --self explanatory

materialname varchar(64) NULL , materialtrans varchar(8) NULL ,

height varchar(64) NULL , heightunits varchar(64) NULL ,

volume varchar(64) NULL , volumeunits varchar(64) NULL ,

haslid varchar(8) NULL , createdate datetime DEFAULT getDATE())

CREATE TABLE dbo.Content (

contentid int IDENTITY (1,1) PRIMARY KEY NONCLUSTERED, --self explanatory

cupid int NOT NULL FOREIGN KEY (cupid) REFERENCES dbo.Cup(cupid), -- contenttype varchar(64) NULL , contentname varchar(64) NULL , quantity int NOT NULL DEFAULT 0,

createdate datetime DEFAULT getDATE())

print 'Tabled Created'

if exists (select * from sysobjects where id = object_id('dbo.uspCreateCups') and sysstat & 0xf = 4) drop procedure dbo.uspCreateCupsGO

CREATE Procedure dbo.uspCreateCups( @xml_doc text)

set nocount ON

DECLARE @idoc int--Sample XML document

--Create an internal representation of the XML document.exec sp_xml_preparedocument @idoc OUTPUT, @xml_doc-- SELECT statement using OPENXML rowset provider

--Create a variable table to insert the values--this allows a bulk insert laterDECLARE @myvariabletableCups table( materialname varchar(64) NULL , materialtrans varchar(8) NULL , height varchar(64) NULL , heightunits varchar(64) NULL , volume varchar(64) NULL , volumeunits varchar(64) NULL , haslid varchar(8) NULL )

DECLARE @myvariabletableContent table(

contenttype varchar(64) NULL , contentname varchar(64) NULL , quantity int NOT NULL DEFAULT 0)

/*<CUP><MATERIAL transparent="yes">glass</MATERIAL><HEIGHT units="inches">6</HEIGHT><VOLUME units="ounces">16</VOLUME><CONTENTS> <SOLID qty="2">ice cube</SOLID> <SOLID qty="1">straw</SOLID> <LIQUID qty="3" units="ounces">water</LIQUID> <OTHER qty="0"/></CONTENTS><LID>yes</LID></CUP>*/

--but only the needed data is actually inserted into the real tablesINSERT INTO @myvariabletableCupsSELECT materialname , materialtrans , height , heightunits , volume , volumeunits , haslid

FROM OPENXML (@idoc, '/CUP',2) --The "2" means something. See Books Online. WITH ( materialname varchar(64) './MATERIAL' , materialtrans varchar(64) './MATERIAL/@transparent' , height varchar(64) './HEIGHT', heightunits varchar(64) './HEIGHT/@units', volume varchar(64) './VOLUME', volumeunits varchar(64) './VOLUME/@units', haslid varchar(8) './LID'

print '< select * from @myvariabletableCups >'select * from @myvariabletableCups

INSERT INTO @myvariabletableContentSELECT 'SOLID, not perfect example with in-consistent Xpath' , contentname , quantity

FROM OPENXML (@idoc, '/CUP/CONTENTS/SOLID',2) --The "2" means something. See Books Online. WITH ( contenttype varchar(64) , contentname varchar(64) '.' , quantity varchar(64) './@qty'

print '< select * from @myvariabletableContent >'select * from @myvariabletableContent

BEGIN TRAN

INSERT INTO dbo.Cup ( materialname , materialtrans , height , heightunits , volume , volumeunits , haslid SELECT materialname , materialtrans , height , heightunits , volume , volumeunits , haslid

FROM @myvariabletableCups

DECLARE @cupJustInsertedIdentity intSELECT @cupJustInsertedIdentity = IDENT_CURRENT('dbo.Cup')

if (@cupJustInsertedIdentity IS NOT NULL) BEGIN

INSERT INTO dbo.Content ( cupid , contenttype , contentname , quantity SELECT @cupJustInsertedIdentity , contenttype , contentname , quantity FROM @myvariabletableContent

END

IF @@ERROR <> 0 BEGIN RAISERROR('insert error', 16, 1) ROLLBACK Transaction END ELSE BEGIN COMMIT TRAN END

print '< select * from dbo.Cup >'select * from dbo.Cup

print '< select * from dbo.Content >'select * from dbo.Content

SET NOCOUNT OFF

print 'Procedure Created'

Now, to use it:

EXEC dbo.uspCreateCups'<?xml version="1.0"?><CUP><MATERIAL transparent="yes">glass</MATERIAL><HEIGHT units="inches">6</HEIGHT><VOLUME units="ounces">16</VOLUME><CONTENTS> <SOLID qty="2">ice cube</SOLID> <SOLID qty="1">straw</SOLID> <LIQUID qty="3" units="ounces">water</LIQUID> <OTHER qty="0"/></CONTENTS><LID>yes</LID></CUP>'

ASP and ADO Gotcha - Duplicate Field Names in a Query

Leon Platt — Mon, 16 Jul 2001 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lplatt/aspandadogotchaduplicatefieldnamesinaquery.asp

Pseudo web service using xmlHTTP

Leon Platt — Mon, 16 Jul 2001 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lplatt/pseudowebserviceusingxmlhttp_1.asp

ASP - reaping the benifits of SQL 2000's FOR XML