[ExcelNerd]

Thanks for any insights into this.

The parameter strInsertXML is an XML string. When I run the stored procedure from SQL Server Management Studio the records in the XML go into the table nicely. But when I fire the same XML into the web browser invoking the web service the Insert does not happen (it returns 0).

The XML string strInsertXML

<?xml version="1.0"?><Order OrderID="1001" CustomerID="ALFKI" EmployeeID="1" OrderDate="01/01/2001"><Items><Item ProductID="11" Qty="1" UnitPrice="12.99"><Discount>0</Discount></Item><Item ProductID="17" Qty="2" UnitPrice="4.99"><Discount>0.5</Discount></Item><Item ProductID="21" Qty="1" UnitPrice="11.99"><Discount>0</Discount></Item></Items></Order>

The stored procedure

CREATE procedure [dbo].[proc_greamemalcolm_example] @xmlInput as varchar(2000)

as

-- Create Temporary Tables to simulate the order and order detail tables
CREATE TABLE #Orders
(OrderID INTEGER NOT NULL ,
CustomerID nCHAR (5) NULL ,
EmployeeID INTEGER NULL ,
OrderDate DATETIME NULL)

CREATE TABLE #OrderDetails
( OrderID INTEGER NOT NULL,
ProductID INTEGER NOT NULL,
UnitPrice MONEY NOT NULL,
Quantity SMALLINT NOT NULL,
Discount REAL NOT NULL )

DECLARE @iTree INTEGER
DECLARE @xmlOrder VARCHAR(2000)

-- Simulate an order
SET @xmlOrder = @xmlInput

EXEC sp_xml_preparedocument @iTree OUTPUT, @xmlOrder

--Insert Order Record
INSERT #Orders (OrderID, CustomerID, EmployeeID, OrderDate)
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM
OPENXML(@iTree, 'Order', 1)
WITH ( OrderID INTEGER,
EmployeeID INTEGER,
OrderDate DATETIME,
CustomerID nCHAR(5))

-- Insert Order Details Record
INSERT #OrderDetails
SELECT * FROM
OPENXML(@iTree, 'Order/Items/Item', 1)
WITH ( OrderID INTEGER '../../@OrderID',
ProductID INTEGER,
Qty INTEGER,
UnitPrice MONEY,
Discount REAL 'Discount')

-- Clear the XML from memory
EXEC sp_xml_removedocument @iTree

drop table tbl_GreameMalcolm1
drop table tbl_GreameMalcolm2

SELECT * 
into tbl_GreameMalcolm1
FROM #Orders

SELECT * 
into tbl_GreameMalcolm2
FROM #OrderDetails

This is the Web Method.

   <WebMethod()> _
    Public Function GreameMalcolm(ByVal strInsertXML As String) As Integer

        Dim connStr As String = "Data Source=George4;Initial Catalog=Cookbook;Persist Security Info=True;User ID=sa;Password=xxxxxx"

        Dim cmdTxt As String = "exec proc_greamemalcolm_example '" & strInsertXML & "')"

        Dim myConn As New System.Data.SqlClient.SqlConnection(connStr)
        Dim myCmd As New System.Data.SqlClient.SqlCommand(cmdTxt, myConn)

        Try

            myConn.Open()
            myCmd.ExecuteNonQuery()

        Catch ex As Exception
            ' handle exception
        End Try

        If myConn.State = Data.ConnectionState.Open Then myConn.Close()

        myConn.Dispose()
        myCmd.Dispose()

    End Function

Does anybody know why I get a different result? Is there a better code sample for inserting a set of records (flat) into a table via a ASP.NET web service in VB.NET?
(I am grateful to the code sample by Greame Malcolm I found on the net that got me this far. Thanks Greame)

Thanks!