Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP XML
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP XML Using ASP 3 and XML. See also the XML category for more XML discussions not relating to ASP. NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP XML section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 21st, 2003, 04:42 AM
Registered User
 
Join Date: Jul 2003
Location: , , Singapore.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to import xml into SQL database thru ASP

hi!:D

this is the first time i'm using the forum to post.I have a question.

i have a XML file with the records that i wanted to update into the database. How should i get on doing that using ASP?

//How the XML file look like
<contact>
   <person id="1">
       <name>Tom</person>
       <phone>123456</phone>
       <section>HR</section>
       <designation>Admin</designation>
   </person>
</contact>



Reply With Quote
  #2 (permalink)  
Old July 30th, 2003, 03:23 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dim xmlData, nodPerson, sSQL, sID, sName, sPhone, sSection, sDesign
Set xmlData = Server.CreateObject("Msxml2.DOMDocument.4.0")
Set nodPerson = xmlData.documentElement.selectSingleNode("person[@id='1']")

sID = nodPerson.getAttribute("id")
sName = nodPerson.selectSingleNode("name").text
sPhone = nodPerson.selectSingleNode("phone").text
sSection = nodPerson.selectSingleNode("section").text
sDesign = nodPerson.selectSingleNode("designation").text

'assume all fields are alphanumeric (varchar, char,...) and id is integer
sSQL = "UPDATE your_table SET name='" & sName & "', phone='" & sPhone & "', section='" & sSection & "', designation='" & sDesign & "' WHERE id=" & sID

'assume you have an opened connection (you didn't tell what type of DB you use)
cn.Execute(sSQL)

'you can also send the whole xml document to SQL Server 2000 as a ntext field, i can send you example if you want to know

Hope this help
Khoa Nguyen
Reply With Quote
  #3 (permalink)  
Old August 29th, 2003, 12:11 PM
Registered User
 
Join Date: Jul 2003
Location: , , Singapore.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks a lot...i have got it:D

Reply With Quote
  #4 (permalink)  
Old October 9th, 2003, 02:15 AM
Registered User
 
Join Date: Oct 2003
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Sasina
Default

Hi tknguyen!

I'd like to see that example of sending the whole xml document to SQL Server 2000 as a ntext field

Thank's a lot

Reply With Quote
  #5 (permalink)  
Old October 14th, 2003, 11:46 AM
Registered User
 
Join Date: Oct 2003
Location: Bs As, Capital Federal, Argentina.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi people,
I'm using this method

in VB i'm passing the xml to the parameter of the object command, and use the output parameter to check if any error ocurred


this is the stored procedure

ALTER PROC Piezas_TXRetarifarXML
@XMLPieza TEXT,
@Salida CHAR(1) OUT
AS

DECLARE @hDoc INT
DECLARE @CoeficienteAforo INT
DECLARE @IdOperativa INT
DECLARE @Volumen NUMERIC(9,5)
DECLARE @PesoAforado NUMERIC(9,2)
DECLARE @IdPieza INT
DECLARE @IdUsuario INT

SET NOCOUNT ON

    SET @Salida = '0'

    --Creo Puntero para el XML
    EXEC sp_xml_preparedocument
        @hDoc OUTPUT ,
        @XMLPieza

    --Inserto el XML en tablas Temporales
    --para poder liberarlas


    --Paquetes
DECLARE @TBL_Paquetes TABLE
(
    idpieza INT,
    idpaquete INT,
    alto NUMERIC(9,2),
    ancho NUMERIC(9,2),
    largo NUMERIC(9,2),
    peso NUMERIC(9,2),
    volumen NUMERIC(9,5),
    valor MONEY
)

    INSERT INTO @TBL_Paquetes
    SELECT Idpieza,
                IdPaquete,
                Alto,
                Ancho,
                Largo,
                Peso,
                Volumen,
                Valor
    FROM OPENXML (@hDoc, '/ROWS/paquetes/paquete',1)
        WITH
            (
                idpieza INT,
                idpaquete INT,
                alto NUMERIC(9,2),
                ancho NUMERIC(9,2),
                largo NUMERIC(9,2),
                peso NUMERIC(9,2),
                volumen NUMERIC(9,5),
                valor MONEY
            )

    --Parametros
DECLARE @TBL_Parametros TABLE
(
    idpieza INT,
    volumen NUMERIC(9,5),
    pesototal NUMERIC(9,2),
    cantidadpaquetes INT,
    nrooperativa INT,
    idcodpostalorigen INT,
    idcodpostaldestino INT,
    nrofactura VARCHAR(13),
    valordeclarado MONEY,
    seguro MONEY,
    precioenvio MONEY,
    idusuario INT
)



    INSERT INTO @TBL_Parametros
    SELECT IdPieza,
                Volumen,
                PesoTotal,
                CantidadPaquetes,
                NroOperativa,
                IdCodPostalOrigen,
                IdCodPostalDestino,
                NroFactura,
                ValorDeclarado,
                Seguro,
                PrecioEnvio,
                IdUsuario
    FROM OPENXML (@hDoc, '/ROWS/parametros/parametro',1)
        WITH
            (
                idpieza INT,
                volumen NUMERIC(9,5),
                pesototal NUMERIC(9,2),
                cantidadpaquetes INT,
                nrooperativa INT,
                idcodpostalorigen INT,
                idcodpostaldestino INT,
                nrofactura VARCHAR(13),
                valordeclarado MONEY,
                seguro MONEY,
                precioenvio MONEY,
                idusuario INT
            )

    --Tipos Precios
    DECLARE @TBL_Piezas_Precios TABLE
    (
        IdTipoPrecio CHAR(1),
        Valor MONEY
    )

    INSERT INTO @TBL_Piezas_Precios
    SELECT IdTipoPrecio,
                Valor
    FROM OPENXML (@hDoc, '/ROWS/tiposprecios/tipoprecio',1)
        WITH
            (
                idtipoprecio CHAR(1),
                valor MONEY
            )
    --Elimino el Puento para el XML
    EXEC sp_xml_removedocument @hDoc

--Comienza la Tranzaccion para Grabar
BEGIN TRAN

        --Actualizo los Paquetes
        UPDATE PaquetesPorEnvio WITH(ROWLOCK)
            SET Alto = T.Alto,
                    Ancho = T.Ancho,
                    Largo = T.Largo,
                    Peso = T.Peso,
                    Volumen = T.Volumen,
                    Valor = T.Valor
        FROM PaquetesPorEnvio P WITH(NOLOCK)
        INNER JOIN @TBL_Paquetes T
        ON P.IdPaquete = T.IdPaquete
        AND P.IdPieza = T.IdPieza
        IF @@ERROR <> 0
        GOTO MAL

        SELECT @IdOperativa = NroOperativa,
                    @Volumen = Volumen,
                    @IdPieza = IdPieza,
                    @IdUsuario = IdUsuario
        FROM @TBL_Parametros

        --Calculo el Peso Aforado
        SELECT @CoeficienteAforo = ISNULL(CoeficienteAforo,0)
        FROM Operativas WITH(NOLOCK)
        WHERE PesoAforado = 1
        AND IdOperativa = @IdOperativa

        SET @PesoAforado = @Volumen * @CoeficienteAforo

        --Actualizo la Pieza
        UPDATE Piezas WITH(ROWLOCK)
        SET PesoTotal = T.PesoTotal,
                PesoAforado = ISNULL(@PesoAforado,0),
                VolumenTotal = T.Volumen,
                CantidadPaquetes = T.CantidadPaquetes,
                NroRemito = T.Nrofactura,
                ImporteRemito = T.ValorDeclarado,
                Seguro = T.Seguro,
                PrecioEnvio = T.PrecioEnvio
        FROM Piezas P WITH(NOLOCK)
        INNER JOIN @TBL_Parametros T
        ON P.IdPieza = T.IdPieza

        IF @@ERROR <> 0
        GOTO MAL

        --Elimino los tipos de Precios
        DELETE FROM Piezas_Precios WITH(ROWLOCK)
        WHERE IdPieza = @IdPieza
        IF @@ERROR <> 0
        GOTO MAL

    --Inserto los tipos de Precios
    IF (
        SELECT TOP 1 LEN(IdTipoPrecio)
        FROM @TBL_Piezas_Precios
       ) > 0
    BEGIN
        INSERT INTO Piezas_Precios WITH(ROWLOCK)
        SELECT @IdPieza,
                    IdTipoPrecio,
                    Valor,
                    GETDATE(),
                    @IdUsuario
        FROM @TBL_Piezas_Precios
        IF @@ERROR <> 0
        GOTO MAL
    END

MAL:
    IF @@ERROR <> 0
        BEGIN
            SET @Salida = '0'
            ROLLBACK TRAN
        END
    ELSE
        BEGIN
            SET @Salida = '1'
            COMMIT TRAN
        END

IF @@TRANCOUNT > 0
    BEGIN
        SET @Salida = '0'
        ROLLBACK TRAN
    END

Sorry for my bad english, good luck!

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
import from excel to sql database manigandan C# 2 March 5th, 2010 05:34 AM
Import a XML file into SQL Server tables Cha BOOK: Professional SQL Server 2005 Integration Services ISBN: 0-7645-8435-9 0 April 1st, 2008 09:54 PM
How to import XML into SQL Server dbellavi XML 1 February 26th, 2008 06:02 AM
Import XML to SQL table Colonel Angus SQL Server 2000 4 March 23rd, 2007 08:37 AM
Import XML and its attribute into SQL server 2000 khautinh C# 0 April 13th, 2005 09:57 AM



All times are GMT -4. The time now is 03:54 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.