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>

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

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

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

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!