• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

SQL Server 2005 stored procedure doesnt work

Experts,
I am sending an XML string to a SQL Server stored procedure to insert data into a table. I get no errors from sql server, but no data is saved to the table either. The XML string is:
<DocumentElement>
  <CEvents>
    <BLEventID>358090817570</BLEventID>
    <ContactTS>2009-12-24T08:17:36-05:00</ContactTS>
    <CallPurpose />
    <CallerID />
    <CallerName>Nick</CallerName>
    <CallerType />
    <CallLen>299</CallLen>
    <AddedBy>Q345346</AddedBy>
    <Entity_ID>1</Entity_ID>
    <ConMethod />
    <Event>Communication</Event>
    <EventTS>2009-12-24T08:17:36-05:00</EventTS>
    <SubAction>Seminar</SubAction>
    <Comment />
  </CEvents>
</DocumentElement>

The Stored procedure is:
Create PROCEDURE uspInsertContact
      
      @xmlData xml
AS
BEGIN
      SET NOCOUNT ON;

   INSERT INTO dbo.CALL_CONTACT
    (Broker_Liaison_Event_ID, Caller_Contact_dts, Call_Purpose_Global_Cd,
Caller_Contact_User_ID, Caller_Nm, Caller_Type_Global_Cd,
      Call_Length_Nbr, Contact_Event_User_ID, Entity_ID)
      SELECT
            x.d.value('BLEventID[1]','bigint') AS Broker_Liaison_Event_ID,
            CAST(x.d.value('ContactTS[1]','varchar(30)') AS DateTime)AS Caller_Contact_dts,
            x.d.value('CallPurpose[1]','Varchar(25)') AS Call_Purpose_Global_Cd,
            x.d.value('CallerID[1]','Varchar(10)') AS Caller_Contact_User_ID,
            x.d.value('CallerName[1]','Varchar(40)') AS Caller_Nm,
            x.d.value('CallerType[1]','Varchar(8)') AS Caller_Type_Global_Cd,
            x.d.value('CallLen[1]','INT') AS Call_Length_Nbr,
            x.d.value('AddedBy[1]','Varchar(10)') AS Contact_Aetna_User_ID,
            x.d.value('EntityID[1]','INT') AS Entity_ID
      FROM @xmlData.nodes('/NewDataSet/Table1') x(d)
END

Appreciate any assistance in getting this data saved to the table!!
0
JohnPell
Asked:
JohnPell
  • 8
  • 7
  • 3
1 Solution
 
DCMBSCommented:
Try

SELECT
            d.value('BLEventID[1]','bigint') AS Broker_Liaison_Event_ID,
            CAST(x.d.value('ContactTS[1]','varchar(30)') AS DateTime)AS Caller_Contact_dts,
            d.value('CallPurpose[1]','Varchar(25)') AS Call_Purpose_Global_Cd,
            d.value('CallerAetnaID[1]','Varchar(10)') AS Caller_Contact_User_ID,
            d.value('CallerName[1]','Varchar(40)') AS Caller_Nm,
            d.value('CallerType[1]','Varchar(8)') AS Caller_Type_Global_Cd,
            d.value('CallLen[1]','INT') AS Call_Length_Nbr,
            d.value('AddedBy[1]','Varchar(10)') AS Contact_Event_User_ID,
            d.value('EntityID[1]','INT') AS Entity_ID
      FROM @xmlData.nodes('/NewDataSet/Table1') x(d)
0
 
DCMBSCommented:
Also the last line may need to be

 FROM @xmlData.nodes('/NewDataSet/Table1') as x(d)
0
 
JohnPellAuthor Commented:
DCMBS, Same results, no errors, but nothing is saved. I have been able to save data to the table thru a stored procedure, just not with XML. However, I will need to send it lists, so I need to get this working. Thanks for your idea.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
JohnPellAuthor Commented:
DCMBS, tried changing the last line with the same results, no record saved
0
 
DCMBSCommented:
I think we are on the right lines, have a look at the following link.  It may help

http://stackoverflow.com/questions/1743714/insert-xml-data-into-sql-tables
0
 
JohnPellAuthor Commented:
Tried the items from this link again with no success. Appreciate the info thou
0
 
JohnPellAuthor Commented:
with further testing, the following returns the correct value

set @Ret = (SELECT
      x.d.value('CallLen[1]','INT')
      FROM @xmlData.nodes('/DocumentElement/CEvents') as x(d))
      return @Ret
0
 
DCMBSCommented:
This should not be deleted as it contains the solution to the question.  JohnPell should accept his own answer and make it PAQ'ed so that others may benefit.
0
 
JohnPellAuthor Commented:
My last comment is not a solution as the data is still not being saved. I have yet to get this to work. I would like it deleted please
0
 
JohnPellAuthor Commented:
Sure, but I was hoping to delete my company name from the examples I sent
0
 
Mark WillsTopic AdvisorCommented:
Always try it long hand first :


declare @xmldata xml
set @xmldata = '<DocumentElement>
  <CEvents>
    <BLEventID>358090817570</BLEventID>
    <ContactTS>2009-12-24T08:17:36-05:00</ContactTS>
    <CallPurpose />
    <CallerID />
    <CallerName>Nick</CallerName>
    <CallerType />
    <CallLen>299</CallLen>
    <AddedBy>Q345346</AddedBy>
    <Entity_ID>1</Entity_ID>
    <ConMethod />
    <Event>Communication</Event>
    <EventTS>2009-12-24T08:17:36-05:00</EventTS>
    <SubAction>Seminar</SubAction>
    <Comment />
  </CEvents>
</DocumentElement>'

      SELECT
            x.d.value('BLEventID[1]','bigint') AS Broker_Liaison_Event_ID,
            x.d.value('ContactTS[1]','char(25)') AS Caller_Contact_dts,
            x.d.value('CallPurpose[1]','Varchar(25)') AS Call_Purpose_Global_Cd,
            x.d.value('CallerID[1]','Varchar(10)') AS Caller_Contact_User_ID,
            x.d.value('CallerName[1]','Varchar(40)') AS Caller_Nm,
            x.d.value('CallerType[1]','Varchar(8)') AS Caller_Type_Global_Cd,
            x.d.value('CallLen[1]','INT') AS Call_Length_Nbr,
            x.d.value('AddedBy[1]','Varchar(10)') AS Contact_Aetna_User_ID,
            x.d.value('EntityID[1]','INT') AS Entity_ID
      FROM @xmldata.nodes('/DocumentElement/CEvents') x(d)

That seems to work - the nodes have to reflect the right part of the tree.
Now, I have never had too much joy with style code 127 for dates - sometimes having to truncate the last 6 characters (ie the zone) and just use style code 126 to convert the date.

e.g.

select convert(datetime,'2009-12-24T08:17:36.000',126)  -- works fine
select convert(datetime,'2009-12-24T08:17:36.000-05:00',127)  -- sometimes cracks up

Now, as for inserting into your table, then just do the same above interactively, this time include the insert statement (and a test area / test table). Could be some kind of constraint happening within the table (maybe). At least interactively, it will show you the errors fairly explicitly...
0
 
DCMBSCommented:
The following stored procedure works for me.  

I created a database called TEST and created a table called CALL_CONTACTS as

USE [Test]
GO

/****** Object:  Table [dbo].[CALL_CONTACT]    Script Date: 12/27/2009 15:50:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CALL_CONTACT](
      [Broker_Liaison_Event_ID] [bigint] NULL,
      [Caller_Contact_dts] [varchar](30) NULL,
      [Call_Purpose_Global_Cd] [varchar](25) NULL,
      [Caller_Contact_User_ID] [varchar](10) NULL,
      [Caller_Nm] [varchar](40) NULL,
      [Caller_Type_Global_Cd] [varchar](8) NULL,
      [Call_Length_Nbr] [int] NULL,
      [Contact_Event_User_ID] [varchar](10) NULL,
      [Entity_Id] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


Create the stored procedure
USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[uspInsertContact]    Script Date: 12/27/2009 15:54:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery10.sql|7|0|C:\Users\Denis\AppData\Local\Temp\~vs1FC6.sql
ALTER PROCEDURE [dbo].[uspInsertContact]
     
      @xmlData xml

AS
BEGIN
      SET NOCOUNT ON;

      INSERT INTO dbo.CALL_CONTACT
    (Broker_Liaison_Event_ID, Caller_Contact_dts, Call_Purpose_Global_Cd,
Caller_Contact_User_ID, Caller_Nm, Caller_Type_Global_Cd,
      Call_Length_Nbr, Contact_Event_User_ID, Entity_ID)
       SELECT
            x.d.value('(BLEventID)[1]','bigint') AS Broker_Liaison_Event_ID,
            x.d.value('(ContactTS)[1]','varchar(30)') AS Caller_Contact_dts,
            x.d.value('(CallPurpose)[1]','varchar(25)') AS Call_Purpose_Global_Cd,
            x.d.value('(CallerID)[1]','varchar(10)') AS Caller_Contact_User_ID,
            x.d.value('(CallerName)[1]','varchar(40)') AS Caller_Nm,
            x.d.value('(CallerType)[1]','varchar(8)') AS Caller_Type_Global_Cd,
            x.d.value('(CallLen[1])','int') AS Call_Length_Nbr,
            x.d.value('(AddedBy[1])','varchar(10)') AS Contact_Event_User_ID,
            x.d.value('(EntityID[1])','int') AS Entity_ID
      FROM @xmlData.nodes('/DocumentElement/CEvents') x(d)
     
END

Execute the stored procedure
USE [Test]
GO

DECLARE      @return_value int

EXEC      @return_value = [dbo].[uspInsertContact]
            @xmlData = '<?xml version="1.0" ?>
      <DocumentElement>
  <CEvents>
    <BLEventID>358090817570</BLEventID>
    <ContactTS>2009-12-24T08:17:36-05:00</ContactTS>
    <CallPurpose />
    <CallerID />
    <CallerName>Nick</CallerName>
    <CallerType />
    <CallLen>299</CallLen>
    <AddedBy>Q345346</AddedBy>
    <Entity_ID>1</Entity_ID>
    <ConMethod />
    <Event>Communication</Event>
    <EventTS>2009-12-24T08:17:36-05:00</EventTS>
    <SubAction>Seminar</SubAction>
    <Comment />
  </CEvents>
</DocumentElement>'

SELECT      'Return Value' = @return_value

GO

0
 
DCMBSCommented:
Resulting output following execution of the Stored procedure

SELECT *
  FROM [Test].[dbo].[CALL_CONTACT]

Broker_Liaison_Event_ID Caller_Contact_dts             Call_Purpose_Global_Cd    Caller_Contact_User_ID Caller_Nm                                Caller_Type_Global_Cd Call_Length_Nbr Contact_Event_User_ID Entity_Id
----------------------- ------------------------------ ------------------------- ---------------------- ---------------------------------------- --------------------- --------------- --------------------- -----------
358090817570            2009-12-24T08:17:36-05:00                                                       Nick                                                           299             Q345346               NULL

(1 row(s) affected)
0
 
DCMBSCommented:
Best I can do with the date field

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[uspInsertContact]    Script Date: 12/27/2009 17:32:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery10.sql|7|0|C:\Users\Denis\AppData\Local\Temp\~vs1FC6.sql
ALTER PROCEDURE [dbo].[uspInsertContact]
     
      @xmlData xml

AS
BEGIN
      SET NOCOUNT ON;

      INSERT INTO dbo.CALL_CONTACT
    (Broker_Liaison_Event_ID, Caller_Contact_dts, Call_Purpose_Global_Cd,
Caller_Contact_User_ID, Caller_Nm, Caller_Type_Global_Cd,
      Call_Length_Nbr, Contact_Event_User_ID, Entity_ID)
       SELECT
            x.d.value('(BLEventID)[1]','bigint') AS Broker_Liaison_Event_ID,
            Convert(DateTime,substring(x.d.value('(ContactTS)[1]','varchar(30)'),1,19),126) AS Caller_Contact_dts,
            x.d.value('(CallPurpose)[1]','varchar(25)') AS Call_Purpose_Global_Cd,
            x.d.value('(CallerID)[1]','varchar(10)') AS Caller_Contact_User_ID,
            x.d.value('(CallerName)[1]','varchar(40)') AS Caller_Nm,
            x.d.value('(CallerType)[1]','varchar(8)') AS Caller_Type_Global_Cd,
            x.d.value('(CallLen[1])','int') AS Call_Length_Nbr,
            x.d.value('(AddedBy[1])','varchar(10)') AS Contact_Event_User_ID,
            x.d.value('(EntityID[1])','int') AS Entity_ID
      FROM @xmlData.nodes('/DocumentElement/CEvents') x(d)
     
     
     
     
END
0
 
Mark WillsTopic AdvisorCommented:
*laughing* that is kinda what I said DCMBS - including getting the correct node see my post at  http:#26127050

Was hoping to see the results of doing it interactively before going off and modifying stored procedures. Need to ascertain if there are any constraints on the table (such as not null or duplicates etc)
0
 
JohnPellAuthor Commented:
mark, Your code worked, and I was able to see that the issue is the datetime. I tried the conversion as you mentioned with truncating the last 6 char and got this:  convert(datetime, left(x.d.value('ContactTS[1]','varchar(30)'),18),126) AS Caller_Contact_dts. It will get thru the parse of the stored procedure ok, but fails when I try to import the data. How would I truncate the date field so that the convert(datetime, value,126) works?
0
 
JohnPellAuthor Commented:
Great way to locate the issue and resolve it!!
0
 
Mark WillsTopic AdvisorCommented:
Thanks, very happy to have been able to help....

As for that datetime, a few ways, but requires a careful count of the number of characters ;) Here are two :

            convert(datetime,left((x.d.value('ContactTS[1]','varchar(30)')),19),126) AS Caller_Contact_dts_oneway,
            convert(datetime,(x.d.value('ContactTS[1]','char(19)')),126) AS Caller_Contact_dts_anotherway,

It is always a pity that style code 127 does not do the right thing...
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 8
  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now