Help creating XML file in SQL SERVER 2008
-
I've made a procedure that collects data from SQL dB and create an XML-file containing the data.
My problem is that after creating the XML-file I can't read it in the webbrowser. However, if I open it in an editor, i.e Visual Studio, and save it then it's possible for me to see the file in the webbrowser.
(Note: Upon saving it from Visual Studio the filesize alse decreases by 50%)
Can anyone help me with this? I have no clue as how to make this work... :crying:
CREATE PROCEDURE [my_new_sp]
@newVar_1 bigint,
@newVar_2 smallint
AS
BEGIN
SET NOCOUNT ON;
SELECT @newVar_1,
@newVar_2
DECLARE
@XML_1 varchar(8000),
@XML_2 varchar(max),
@XML_3 varchar(8000),
@myXML varchar(max),
@myFile varchar(30),
@var_1 int,
@var_2 int,
@var_3 datetime,
@var_4 varchar(30),
@var_5 varchar(50),
@var_6 varchar(50),
@var_7 varchar(50),
@var_8 varchar(10),
@var_9 varchar(30),
@subVar_1 varchar(30),
@subVar_2 varchar(30),
@subVar_3 varchar(30),
@subVar_4 varchar(30),
@subVar_5 varchar(30),
@subVar_6 varchar(30),
@subVar_7 varchar(30),
@subVar_8 varchar(30)
SET @XML_1 = ''
SET @XML_2 = ''
SET @XML_3 = ''
DECLARE C1 CURSOR READ_ONLY FOR
SELECT
isnull(tbl_1.column_1, ''),
isnull(tbl_1.column_2, ''),
isnull(tbl.column_3, ''),
isnull(tbl.column_4, ''),
isnull(tbl.column_5, ''),
isnull(tbl.column_6, ''),
isnull(tbl.column_7, ''),
isnull(tbl.column_8, ''),
isnull(tbl_3.column_9, '')
FROM tbl_1, tbl_2, tbl_3
WHERE tbl_1.column_2 = @newVar_1
AND tbl_1.column_1 = tbl_2.column_1
AND tbl_3.column_3 = tbl_2.column_1
AND tbl_3.column_1 = tbl_2.column_2
AND tbl_1.column_4 = @newVar_2
AND tbl_2.column_4 = @newVar_2
AND tbl_3.column_4 = @newVar_2
SET @XML_1 = @XML_1 + '<?xml version="1.0" encoding="UTF-8"?>'
+'<ELEMENT_A>'
+'<ELEMENT_B>'
+'<ELEMENT_C>'
OPEN C1
FETCH NEXT FROM C1 INTO
@var_1,@var_2,@var_3,@var_4,@var_5,@var_6,@var_7,@var_8,@var_9
BEGIN
SET @XML_1 = (@XML_1 + '<Greeting>Hello</Greeting>'
+'<element_1>'+ convert(varchar,@var_1)+'</element_1>'
+'<element_2>'+ convert(varchar,@var_2)+'</element_2>'
+'<element_3>'+ convert(varchar(10),cast(@var_3 AS datetime),121)+'</element_3>'
+'<element_4>'+ convert(varchar,@var_4) +'</element_4>'
+'<element_5>'+ convert(varchar,@var_5) +'</element_5>'
+'<element_6>'+ convert(varchar,@var_6) +'</element_6>'
+'<element_7>'+ convert(varchar, @var_7)+'</element_7>'
+'<element_8>'+ convert(varchar,@var_8)+'</element_8>'
+'<element_9>'+ convert(varchar,@var_9)+'</element_9>'
+'</ELEMENT_C>'+ CHAR(13) + CHAR(10))
END
CLOSE C1
DEALLOCATE C1
SET @XML_3 = @XML_3 + '</ELEMENT_B>' +'</ELEMENT_A>'
DECLARE C2 CURSOR READ_ONLY FOR
SELECT
DISTINCT(isnull(subTbl_1.column_1, '')),
isnull(subTbl_1.column_2, ''),
isnull(subTbl_1.column_3, ''),
isnull(subTbl_1.column_4, ''),
isnull(subTabl_1.column_5, ''),
isnull(subTbl_2.column_4, ''),
isnull(subTbl_3.column_5, ''),
isnull(subTbl_3.column_6, '')
FROM subTbl_1
JOIN subTbl_2 ON subTbl_1.column_2=subTbl_2.column_1
JOIN subTbl_3 ON subTbl_1.column_8=subTbl_3.column_1
WHERE subTbl_1.column_5 = @newVar_1
AND subTbl_1.column_6 = @newVar_2
OPEN C2
FETCH NEXT FROM C2
INTO @subVar_1,@subVar_2,@subVar_3,@subVar_4,@subVar_5,@subVar_6,@subVar_7,@subVar_8
WHILE (@@FETCH_STATUS = 0 )
BEGIN
SET @XML_2 = @XML_2 + '<ELEMENT_D>'
SET @XML_2 = @XML_2 + '<element_1>Default Value</element_1>'
+'<element_2>'+ convert(varchar,@subVar_1)+'</element_2>'
+'<element_3>'+ convert(varchar,@subVar_2)+'</element_3>'
+'<element_4>'+ convert(varchar,@subVar_3)+'</element_4>'
+'<element_5>'+ convert(varchar,@subVar_4)+'</element_5>'
+'<element_6>'+ convert(varchar,@subVar_5)+'</element_6>'
+'<element_7>'+ convert(varchar,@subVar_6)+'</element_7>'
+'<element_8>'+ convert(varchar,@subVar_7)+'</element_8>'
+'<element_9>'+ convert(varchar,@subVar_8)+'</element_9>'
SET @XML_2 = (@XML_2 + '</ELEMENT_D>'+ CHAR(13) + CHAR(10))
FETCH NEXT FROM C2 INTO @subVar_1,@subVar_2,@subVar_3,@subVar_4,@subVar_5,@subVar_6,@subVar_7,@subVar_8
END
CLOSE C2
DEALLOCATE C2
UPDATE tbl_1 SET column_11=1 WHERE column_2=@newVar_1 AND column_4=@newVar_2
SET @myXML = LTRIM(RTRIM(@XML_1 + @XML_2 + @XML_3))
SET @myFile = 'filename_'+ convert(varchar,@newVar_1) +'.xml'
execute my_sp_WriteStringToFile @myXML, 'C:\Folder\subFolder\', @myFile
END
GO
-
It looks like my_sp_WriteStringToFile is saving the file using unicode instead of ansi. If you can't change it, try removing the 'encoding="UTF-8"' from the header.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Worked like a charm! Tnx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply