Could not able to insert the xml data into a table

Question

When i try to insert the values into the table am getting the below error. We cant remove the space in the XML because it is generated from Javascript. How to insert the below data into the XMLdata table.

Conversion failed when converting date and/or time from character string.

This is the sample data(@bbhdn5):

341300-02-1|04/10/2018 01:18:29|04/10/2018 06:18:29|133072;
261600-01-1|04/10/2018 06:18:29|04/10/2018 11:18:29|133073;
781100-R1-1|04/10/2018 11:18:29|04/10/2018 16:18:29|133074;

Code:

create table WMC_Savexmldata 
(
     XML nvarchar(max)
)

Declare @bbhdn5 nvarchar(max) 
set @bbhdn5='341300-02-1|04/10/2018 01:18:29|04/10/2018 06:18:29|133072; 261600-01-1|04/10/2018 06:18:29|04/10/2018 11:18:29|133073; 781100-R1-1|04/10/2018 11:18:29|04/10/2018 16:18:29|133074;'

insert into WMC_Savexmldata
    select @bbhdn5

Post code that we can run that shows the error. I don't see the declaration for the @bbhdn5 variable or assignment. — Dan Guzman, Mar 31 '18 at 11:43
Declare @bbhdn5 nvarchar(max) set @bbhdn5='341300-02-1|04/10/2018 01:18:29|04/10/2018 06:18:29|133072; 261600-01-1|04/10/2018 06:18:29|04/10/2018 11:18:29|133073; 781100-R1-1|04/10/2018 11:18:29|04/10/2018 16:18:29|133074;' — Daniel Stephen, Mar 31 '18 at 11:51
I added the code from your comment you your question where it belongs. This does not reproduce an error. My guess is that you may have a trigger on the table that is the problem. — Dan Guzman, Mar 31 '18 at 11:56
It's totally uncledar what you after, your posted code runs perfectly fine. It creates table with only column of type `varchar` and it inserts value of same type... What's the question? — Michał Turczyn, Mar 31 '18 at 11:56
Ex: when the date comes to the next line in the variable it throws the error. 04/10/2018 11:18:29 Here i cant use enter. so it is coming in the single line. Please try inserting when the comes to the next line like date in one line and time in one line. Then try to insert it will through a error — Daniel Stephen, Mar 31 '18 at 13:02
I cannot see any XML data? Are you talking about CSV-data? Do you want to split this up into three rows? Three rows of 4 columns each? Very strange question... — Shnugo, Mar 31 '18 at 16:32

Shnugo · Accepted Answer · 2018-04-03T08:40:31.400

Although this question is absolutely misleading, my magic crystal ball started to blink suddenly and told me, that you might be looking for this:

Replacing the delimiters ; and | allows to transfer your CSV-string to this XML:

<x>
  <y>341300-02-1</y>
  <y>04/10/2018 01:18:29</y>
  <y>04/10/2018 06:18:29</y>
  <y>133072</y>
</x>
<x>
  <y> 261600-01-1</y>
  <y>04/10/2018 06:18:29</y>
  <y>04/10/2018 11:18:29</y>
  <y>133073</y>
</x>
<x>
  <y> 781100-R1-1</y>
  <y>04/10/2018 11:18:29</y>
  <y>04/10/2018 16:18:29</y>
  <y>133074</y>
</x>

I use this to get the data as derived table:

DECLARE @bbhdn5 NVARCHAR(MAX); 
SET @bbhdn5=N'341300-02-1|04/10/2018 01:18:29|04/10/2018 06:18:29|133072; 261600-01-1|04/10/2018 06:18:29|04/10/2018 11:18:29|133073; 781100-R1-1|04/10/2018 11:18:29|04/10/2018 16:18:29|133074;';

WITH Splitted AS
(
    SELECT CAST('<x><y>' + REPLACE(REPLACE(@bbhdn5,'|','</y><y>'),';','</y></x><x><y>') + '</y></x>' AS XML) AS Casted
)
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNumber
        ,A.x.value('y[1]','nvarchar(max)') AS RowCode
        ,CONVERT(DATETIME,A.x.value('y[2]','nvarchar(max)'),103) AS Date1
        ,CONVERT(DATETIME,A.x.value('y[3]','nvarchar(max)'),103) AS Date2
        ,A.x.value('y[4]','int') AS SomeNumber
FROM Splitted
CROSS APPLY Casted.nodes('/x[y/text()]') AS A(x);

The result

+-----------+-------------+-------------------------+-------------------------+------------+
| RowNumber | RowCode     | Date1                   | Date2                   | SomeNumber |
+-----------+-------------+-------------------------+-------------------------+------------+
| 1         | 341300-02-1 | 2018-10-04 01:18:29.000 | 2018-10-04 06:18:29.000 | 133072     |
+-----------+-------------+-------------------------+-------------------------+------------+
| 2         | 261600-01-1 | 2018-10-04 06:18:29.000 | 2018-10-04 11:18:29.000 | 133073     |
+-----------+-------------+-------------------------+-------------------------+------------+
| 3         | 781100-R1-1 | 2018-10-04 11:18:29.000 | 2018-10-04 16:18:29.000 | 133074     |
+-----------+-------------+-------------------------+-------------------------+------------+

UPDATE

Change the line within the CTE Splitted to this

    SELECT CAST('<x><y>' + REPLACE(REPLACE(REPLACE(REPLACE(@bbhdn5,CHAR(10),' '),CHAR(13),' '),'|','</y><y>'),';','</y></x><x><y>') + '</y></x>' AS XML) AS Casted

This will replace CHAR(13) and CHAR(10) with blanks. Any odd line break within your input should disappear. Well, you might have some additional blanks in string values. But you can replace doubled blanks with single blanks again...

score 0 · Answer 2 · answered Apr 01 '18 at 06:13

insert into WMC_Savexmldata select '496200-01-1|03/31/2018 11:18:29|03/31/2018 16:18:29|133015;245000-01-1|03/31/2018 16:18:29|03/31/2018 21:18:29|133017;262100-13-1|03/31/2018 21:18:29|04/01/2018 02:18:29|133018;'

Please insert the data like this(attached). Date comes in one line and time comes in another line and then run the below query. You will be getting the date time conversion error because of the space error in between date and time. I just want to breaks between the dates. As it is coming from browser the date is coming with space in the SQL. Please help on this. Are you clear with my question.

Declare @InputSepTmp table ( id int, Inputs nvarchar(max) ) insert into @InputSepTmp Select Row_Number() over (Order By (Select null)) , LTrim(RTrim(B.i.value('(./text())1', 'varchar(max)'))) From (Select x = Cast('' + replace((Select replace(XML,';','§§Split§§') as [*] For XML Path('')),'§§Split§§','')+'' as xml).query('.') from WMC_Savexmldata) as A Cross Apply x.nodes('x') AS B(i)

-- select * from @InputSepTmp
 --- Cursor   --------------------
        SET NOCOUNT ON
        DECLARE @InputID varchar(200)
         DECLARE cur_InputSeparator CURSOR
        STATIC FOR 
    select id from @InputSepTmp where Inputs <> ''
        OPEN cur_InputSeparator
        IF @@CURSOR_ROWS > 0
         BEGIN 
         FETCH NEXT FROM cur_InputSeparator INTO @InputID
         WHILE @@Fetch_status = 0
          BEGIN

          DEclare @FinalInputtmp table
          (
          id int,
          IPValues varchar(100)
          )

        insert into @FinalInputtmp
--        SELECT

-- Split.a.value('.', 'NVARCHAR(max)') AS String
--FROM (SELECT
-- CAST ('' + REPLACE(LTRIM(RTRIM(Inputs)), ',', '') + '' AS XML) AS String
-- from @InputSepTmp T1 where id=@InputID) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

Select Row_Number() over (Order By (Select null)) , LTrim(RTrim(B.i.value('(./text())1', 'varchar(max)'))) From (Select x = Cast('' + replace((Select replace(LTRIM(RTRIM(Inputs)),'|','§§Split§§') as [*] For XML Path('')),'§§Split§§','')+'' as xml).query('.') from @InputSepTmp where id = @InputID) as A Cross Apply x.nodes('x') AS B(i)

--select convert(datetime,'04/12/2018 12:50:08')

insert into WMC_CriticalPath_ScheduledDtls (SWOPACKAGENO,TASKNO,SCHEDULEDSTDATE,SCHEDULEDENDDATE,TRACKID,CreatedDate,ModifiedDate)
 SELECT 'adjb', MAX(CASE WHEN D.RN=1 THEN LTRIM(RTRIM(D.IPValues)) END)[task no]
,MAX(CASE WHEN D.RN=2 THEN  LTRIM(RTRIM(D.IPValues)) END) [start date]
,MAX(CASE WHEN D.RN=3 THEN  LTRIM(RTRIM(D.IPValues)) END) [end date]
,MAX(CASE WHEN D.RN=4 THEN LTRIM(RTRIM(D.IPValues)) END) [id], Getdate(),NULL

FROM( SELECT * ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))RN FROM @FinalInputtmp )D

 delete from @FinalInputtmp

         FETCH NEXT FROM cur_InputSeparator INTO @InputID

         END
        END
        CLOSE cur_InputSeparator
        DEALLOCATE cur_InputSeparator
        SET NOCOUNT OFF

--select * from WMC_CriticalPath_ScheduledDtls select * from WMC_CriticalPath_ScheduledDtls

Is this an answer? Is this additional information to your question? Did you check my answer (which seems to do what you want to achieve here - with just a few lines of code)? — Shnugo, Apr 01 '18 at 09:14
Yes it can be done with your code. but when the date and time comes in two lines. Conversion failure is coming. 341300-02-1|04/10/2018 01:18:29|04/10/2018 06:18:29|133072. after the 04/10/2018 press enter and try to insert and check the separater. — Daniel Stephen, Apr 01 '18 at 09:28
Just use `replace` to get rid of `char(10)` and `char(13)`. Replace them with a blank or an empty string. — Shnugo, Apr 01 '18 at 09:30

Could not able to insert the xml data into a table

2 Answers2

UPDATE