CODEWITHSUNDEEP
sql-serverxml
Codrut Rotaru
Codrut Rotaru

Reputation: 61

sql adds unwanted characters in xml tag

I have a sql procedure that generates a xml whit some data from a table. My problem is that sometimes it generates the xml with errors, and more specific it adds a extra character to one of my xml. It is always the same tag and if i remove the tag it does that with the previous tag. Basically in the same position of the xml no matter the tag. Here is my sql code:

ALTER proc [dbo].[genUserVehicleXML]
@userID nvarchar(450)

as
BEGIN
declare @IMEI nvarchar(138),@totalKM decimal(16,4),@funcH decimal(16,4),                              @stayH decimal(16,4),  @totalH decimal(16,4),@dayKM decimal(16,4)

DECLARE contact_cursor CURSOR FOR  
SELECT IMEI FROM ClientsIMEI where ApplicationUserId=@userID


OPEN contact_cursor;  

 -- Perform the first fetch.  
 FETCH NEXT FROM contact_cursor
 into @IMEI;  

 -- Check @@FETCH_STATUS to see if there are any more rows to fetch.  
 WHILE @@FETCH_STATUS = 0  
 BEGIN  
 -- This is executed as long as the previous fetch succeeds. 
  select @totalKM=cast (sum(Kilometers)/cast(1000 as decimal(16,4)) as   decimal(16,4)) from Properties where IMEI=@IMEI
  select @dayKM=cast (sum(Kilometers)/cast(1000 as decimal(16,4)) as decimal(16,4)) from Properties where IMEI=@IMEI and day(Time_Stamp)=day(getdate())
  select @funcH=cast (sum(Properties.Hours)/cast(3600 as decimal(16,4)) as decimal(16,4)) from Properties where IMEI=@IMEI and EngineStatus=1 and day(Time_Stamp)=day(getdate())
  select @stayH=cast (sum(Properties.Hours)/cast(3600 as decimal(16,4)) as decimal(16,4)) from Properties where IMEI=@IMEI and EngineStatus=0 and day(Time_Stamp)=day(getdate())
  select @totalH=cast (sum(Properties.Hours)/cast(3600 as decimal(16,4)) as decimal(16,4)) from Properties where IMEI=@IMEI
  INSERT INTO dbo.XMLData
  select top 1 P.IDProperty,p.ApplicationUserId,
            P.IMEI,P.Time_Stamp,
            P.Latitude,
            P.Longitude,
            P.Speed,
            P.FuelLevel,
            P.EngineStatus,
            isnull(@dayKM,0),
            C.InitialKilometers+@totalKM,
            isnull(@funcH,0),
            isnull(@stayH,0),
            isnull(@totalH,0)+C.InitialHours,
            isnull(@totalH,0),
            P.Angle,
            P.Altitude,
            P.Satellites,
            C.IDClientsIMEI,
            C.CarNumber,
            C.DriverName,
            C.Model 
            from Properties P join ClientsIMEI C on P.IMEI=c.IMEI where   P.ApplicationUserId=@userID and P.IMEI=@IMEI  order by Time_Stamp desc 
   FETCH NEXT FROM contact_cursor into @IMEI;  
   END  



   CLOSE contact_cursor;  
   DEALLOCATE contact_cursor;   


     DECLARE @fileName NVARCHAR(1000)

     DECLARE @sqlStr VARCHAR(1000)
     DECLARE @sqlCmd VARCHAR(1000)

     SET @fileName = 'C:\gps_beta01\wwwroot\outputXML_'+ @userID +'.xml'

     SET @sqlStr = 'select [IDProperty],[ApplicationUserId], [IMEI],convert(varchar(50),cast([Time_Stamp] as datetime),20) as [Time_Stamp],[Latitude],[Longitude],
convert(numeric(16,0),cast([Speed] as float)) as Speed,convert(numeric(16,0),cast([FuelLevel] as float)) as FuelLevel,[EngineStatus],
convert(numeric(16,2),cast([Kilometers] as float))as Kilometers,[KilometersTotal],[FunctionHours],
[StationHours],[TotalHours],[TotalH],convert(numeric(16,0),cast([Angle] as float)) as Angle,convert(numeric(16,0),cast([Altitude] as float)) as Altitude,
[Satellites],[IDClientsIMEI] as [IDClients],replace([CarNumber],'' '','''') as [Number] ,[DriverName],[Model] from gpsTEST.dbo.XMLDATA for xml path (''Vehicle''), root (''Vehicles'')'


     SET @sqlCmd= 'bcp "'+ @sqlStr+'" QUERYOUT '+@fileName+' -c -t, -T -S' +  @@SERVERNAME;

      EXEC Master..xp_CmdShell @sqlCmd
      delete XMLData



      END

And this is a jpeg with xml after I checked it with a xml online validator: enter image description here

This happens everytime only at that position. Please help cause I don't know what is happening.

Update: this is a link with the xml : https://www.dropbox.com/s/9b10s1x2l85l8lf/outputXML_ccbfcf7e-2086-41ef-9e98-ef0b0084c8f4.xml?dl=0

Upvotes: 1

Views: 216

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

First of all: This is a really ugly approach. There is no need for a cursor at all... It was much better (and easier!) to solve this set-based...

But to your question: Your This happens everytime only at that position is not true. Look at your picture: There is a 1 at the middle of your page. This is not breaking any element name, but - obviously - The XML is somehow broken in parts and theses parts are numbered. I'm quite sure, that there is exactly the same count from the start to the floating 1 as there is from there to the 2 which is breaking your XML element's name. Find out, if these numberes are included in the XML file already...

UPDATE:

As expected your DropBox-link points to valid XML... The only bad is a line break, which should not be there... Cannot see any silly numbers... They come into your file somewhere later...

UPDATE 2:

Lines are terminated with a line break by default. You can find a list of options here: Specify Field and Row Terminators (SQL Server). With XML-BULK-export it is advised to use -r or -N (but no -t)

Upvotes: 1

Codrut Rotaru
Codrut Rotaru

Reputation: 61

The solution is to add -r to the bcp command.

SET @sqlCmd= 'bcp "'+ @sqlStr+'" QUERYOUT '+@fileName+' -c  -T -r -S' +  @@SERVERNAME;

I found the solution here: SQL Server Bcp Export XML Format

Upvotes: 0

Related Questions