# SQL XML Output

Dear Experts,

I have a sql job that produces an XML output from a single table - see code below

@query = 'SELECT  * FROM xmloutput Registration  FOR XML AUTO, TYPE, ELEMENTS',
@templatefile = 'D:\XML_Template_File\bspcxml.tpl'

The bspcxml.tpl template is as below:
<RegistrationList>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</RegistrationList>

This produces the following output:
- <RegistrationList>
- <Registration>
<RegistrationID>4880</RegistrationID>
<Town>Chirnside</Town>
<County></County>
<PostCode>TD11 3UE</PostCode>
<PropertyType>Detached</PropertyType>
<RegistrationType>Sale</RegistrationType>
<Bedrooms>4</Bedrooms>
<Garage>true</Garage>
<Garden>true</Garden>
</Registration>
- <RegistrationList>

What I wouild like to achieve is to have the have the Registration ID to be an attribute of the Registration element rather than a child element, for example:

<RegistrationList>
<Registration RegistrationId="1001">
<Town>Some Town</Town>
etc etc

Can anyone advise on how I can achieve this.

Thanks for any help

###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
I guess you should use FOR XML PATH clause in your T-SQL query:

SELECT
RegistrationID AS [@RegistrationID],
...
FROM
xmloutput
FOR XML PATH('Registration')
0
No "guess",  it is exactly what you should be doing :
create table tst_xmloutput (registrationID int, address1 varchar(100), town varchar(100))
insert tst_xmloutput values (4880,'digby house','some town')
go

SELECT registrationid [@RegistrationID], address1, town FROM xmloutput FOR XML path('registration'), TYPE, ELEMENTS
go

@query = 'SELECT registrationid [@RegistrationID], address1, town FROM tst_xmloutput FOR XML path(''registration''), TYPE, ELEMENTS',
@templatefile = 'D:\XML_Template_File\bspcxml.tpl'

0
Commented:
@mark_wills: The ELEMENTS option is completely unnecessary when using FOR XML PATH clause (it is ignored when you use an alias with @ for a column).
0
True... Old habits, and doesn't hurt, however...

This is all you actually need : SELECT registrationid [@RegistrationID], address1, town FROM xmloutput FOR XML PATH('registration')
0
Commented:
@mark_wills: Isn't it the same piece of code I've written above? ;-)
0
Crikey brejk,

I did say it is not a guess it is what the Asker should be doing. That was in full support of your posting.

The fact that I ignorantly recreated the code with additional "type" and "element" was superfluous and was a distraction from using your code - I did write it from scratch using the Askers original query and old habits do die hard, it was meant to replicate your entry, and dispell the "I guess".

Only difference is I used it in context with an example, that was supposed to be the only difference. It is meant to be your code.

You have been around longer than I have, you are an MVP, so what is going on ? There is absolutely nothing in those comments other than support for you.

On the other hand, it would appear that you believe I have ulterior motives - and correct - for showing that it works and for dispelling any doubts associated with your opening comment of "I guess" - and possibly 20 points for an assist, because that is what I thought was happening - reinforcing and assisting your solution.

Regards,

Mark Wills
0
Author Commented:
Thanks Guys

the select statement provides exactly what I am looking for.  The only outstanding glitch is that when I try and run the following code within QA:

@query = 'SELECT * FROM xmloutputnew Registration FOR XML PATH ('Registration')',
@templatefile = 'D:\XML_Template_File\bspcxml.tpl'

I am getting a 'Incorrect syntax near 'Registration'. error.

If I run just the select statement it works fine

Thanks
0
Commented:
Mark,

I'm here not to fight for the points or to make anyone's nerves fried :-) If you felt wounded in any way, I'm really sorry. I was not going to show the lack of respect to you (thus I'm sorry if it looked differently). And, the fact that I'm an MVP means nothing in terms of my behavior or the correctness of my answers I think. Thanks for your assisting.

Cheers,
Pawel
0
Commented:
@grantballantyne: Mark has already provided a solution for your issue - double the quotes aroud the "Registration" word and the code will run.
0
@brejk : MVP's are beyond reproach they are the epitomy of professionalism and altruism we have in the SQL community - I do indeed put you on a very high pedestal.

@grantballantyne : copy and paste either version from above, or below...

sp_makewebtask @outputfile = 'D:\XML_Export_Files\xml_bspc_2009_tst.xml',
@query = 'SELECT registrationid [@RegistrationID], address1, town FROM tst_xmloutput FOR XML path(''registration'')',
@templatefile = 'D:\XML_Template_File\bspcxml.tpl'

0
Author Commented:
Guys thanks so much for your assistance with this.  The script completes successfully in QA using the code from Mark above, however when I try and view the output XML file I get the following error when viewing in IE:

'The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

--------------------------------------------------------------------------------

Whitespace is not allowed at this location. Error processing resource 'file:///D:/XML_Export_Files/xml_bspc_2009_tst.xml'. ...

<

Any suggestions?

Thanks again for your patience with this - you have more than earned the points already.

Grant
0
Common reason for the first part of the error can be fixed by running : %windir%\Microsoft.NET\Framework\vvv\aspnet_regiis.exe -i    where vvv = version and should look something like: v2.0.50727

Now that "whitespace" part of the message normally suggests an imbedded blank within the tag, and obviously the registrationID does have such an appearance. However, I can open fine on my machine. can you share your XML file  (rename to txt) if it is not too big ?

My test is below (need to rename and swap hyphen for underscore)...
xml-bspc-2009-tst.xml.txt
0
Author Commented:
Mark,

Please find XML output file attached - converted to TXT format as XML not allowed to upload.

The full QA SQL statement I am using is:
FROM xmloutput FOR XML path(''registration'')',
@templatefile = 'D:\XML_Template_File\bspcxml.tpl'

I really appreciate your help with this.

Grant

xml-bspc-2009-tst.txt
0
OK, it seems to be data related. Will track it down. Do you have XMLSpy or similar XML tools ? If you are getting serious about XML, then a good xml tool is going to be important part of your toolkit.

Will be back soon...
0
Well now... There are a few "special characters" roaming around - mainly in description.

E.g. have a look for property <registration RegistrationID="7692"> and see if you can notice the invalid character in the description.

>> with sev pleas featís
>> with sev pleas featis

Notice the last i

So, you are going to have to "cleanse" the data. Few strategies, but it is going to get potentially involved...

Then there is the method to output. It is not respecting keyword, or tag boundaries and so creates a carriage return / line feed part way through a tag name (hence the whitespace).

You might want to consider "rolling your own" a bit more involved, but will be better in the long run... Attached is one that works, albeit a few rogue carriage returns and line feeds still present.

And that really is the basis of a new question I think.

xml-bspc-2009-mw.txt
0
Author Commented:
Mark,

Thanks for this.  I have had a look at your attached output and it looks good to me.

What exactly do you mean by 'rolling my own".

This is all new to me.

Thanks

Grant
0
OK, you are using the sp_makewebtask to generate the output file. That exists right now just for backward compatability and is gone from 2008.

Unless there is a lot more to the format file - to actually make a web page - there are other methods to create output.

When data is reasonably clean and predictable, the "FOR XML" variants work really well. However if the data is not so clean and contains characters that need to be addressed, then you might need to start to intercept the output so you can do your own character replacement.

Those special characters can be seen with things like  " & ... ; "  where the "..." is "amp" or "quot" or a hex code etc. That is the XML replacement characters. There are four (five) and the hex codes that the "FOR XML" can handle - surprisingly in the XML standards there is closer to a hundred of "special characters", but not all of them are widely used.

By "rolling your own", I really meant that you might need to intercept some of those columns before outputting (maybe using a function), and then use something other than sp_makewebtask to generate the output (like BCP), but that would also mean using xp_cmdshell.

When it gets to user generated free format text, I sometimes do it all very much from scratch by creating a procedure to generate an output file formatted exactly the way I want, with line feeds between tags...

brejk might have some other ideas...

In terms of "roll your own" it normally starts with some kind of function to replace unwanted characters. Now, it is possible to use a function in an inline query, so, might be sufficient.

Except, that the output file does have line breaks in the most inconvenient positions... So, we will need to address that as well.

Below is a function that will do the character replacement (with various comments), and a function that "builds" the output from the most fundamental, most basic way to "roll your own"...

But first, need to import some data...

-- step 1 let's use the previous XML output to build a new table - we will call this EE_XML_Registrations.

-- first up, make sure the table doesn't already exist..

If object_id('EE_XML_REGISTRATIONS','U') is not NULL drop table EE_XML_REGISTRATIONS
GO

-- now we will be using XML datatype (loaded from the disk file) and use the OPENXML query to select into our new table.

DECLARE @docHandle int
DECLARE @xml xml

SET @xml = (select * FROM OPENROWSET(BULK 'C:\ee\xml_Export_Files\xml-bspc-2009-mw.xml',SINGLE_BLOB) AS x)       -- import XML file from disk

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xml

SELECT * into EE_XML_REGISTRATIONS                                         -- built our new data table
FROM OPENXML(@docHandle, N'/RegistrationList/registration',2)
WITH (RegistrationID varchar(20) '@RegistrationID',
town varchar(100),
county varchar(100),
postcode varchar(100),
propertytype varchar(100),
registrationtype varchar(100),
bedrooms int,
garage varchar(100),
garden varchar(100),
description varchar(max),

EXEC sp_xml_removedocument @docHandle
GO

-- Now just to check and make sure it all loaded OK...

SELECT * from EE_XML_Registrations
GO

-- And finally we are ready to start doing some real work...

-- first the function to strip out characters

CREATE FUNCTION udf_prepare_xml (@v varchar(max))
RETURNS varchar(max)
AS
BEGIN

declare @i int
declare @c varchar(max)

IF patindex('%[^0-9A-Za-z ]%',@v) > 0      -- only do it if
BEGIN
/*      -- the old way would have been to highlight individual characters for replacement such as below
-- advantage is you can substitute the most appropriate character, and might be worth considering
-- disadvantage is it can be easy to miss one or two

SET @v = replace(@v,'í','i')
SET @v = replace(@v,'£','')
SET @v = replace(@v,'é','e')
SET @v = replace(@v,'ç','c')
SET @v = replace(@v,char(96),char(39))
SET @v = replace(@v,char(145),char(39))
SET @v = replace(@v,char(146),char(39))
SET @v = replace(@v,char(133),'')
SET @v = replace(@v,char(150),'')
SET @v = replace(@v,char(178),'2')
SET @v = replace(@v,char(179),'3')
SET @v = replace(@v,char(188),'1/4')
SET @v = replace(@v,char(189),'1/2')
SET @v = replace(@v,char(190),'3/4')
SET @v = replace(@v,char(13)+char(10)+char(13)+char(10),'')           -- now this is only because of the embedded double
SET @v = replace(@v,char(10)+char(10),'')
*/
-- Now, we can use the "for XML" to get a lot of the replacement done
-- but high order non-printable chars need to be removed so have to loop around
-- advantage is you will get printable chracters. Disadvantage is no opportunity for substitution
SET @C = (select @v for xml path(''))
SET @V = ''
SET @i = 1
WHILE @i <= len(@C)
BEGIN
IF ASCII(SUBSTRING(@C, @i, 1)) < 128 SET @v = @v+substring(@C,@i,1)
SET @i = @i + 1
END
END
RETURN @v

END

GO

-- right, so the "replace" function has been done, and now we get to the very, very, raw method of packaging.
-- will take considerably longer to run than other methods,
-- but a few handfuls of time now is worth hours of time trying to track down problems in a big XML file

CREATE PROCEDURE usp_xml_registrations
AS
BEGIN

SET NOCOUNT ON

DECLARE @xmlfile table (xmlrecord varchar (1000),id int identity	)

DECLARE @RegistrationID varchar(20)
DECLARE @town varchar(100)
DECLARE @county varchar(100)
DECLARE @postcode varchar(100)
DECLARE @propertytype varchar(100)
DECLARE @registrationtype varchar(100)
DECLARE @bedrooms int
DECLARE @garage varchar(100)
DECLARE @garden varchar(100)
DECLARE @description varchar(max)

INSERT @xmlfile VALUES ('<RegistrationList>')

OPEN xml_cursor

WHILE @@FETCH_STATUS = 0
BEGIN

-- Note, we could actaully do the same replacement type stuff here...
--
--		SET @description = replace(@description,'&','&amp;')
--		SET @description = replace(@description,'"','&quot;')
--		SET @description = replace(@description,'<','&lt;')
--		SET @description = replace(@description,'>','&gt;')

-- Or, we can use our function

SELECT @description = dbo.udf_prepare_xml(@description)

INSERT @xmlfile VALUES ('   <registration RegistrationID="'+rtrim(@registrationid)+'">')
INSERT @xmlfile VALUES ('      <town>'+rtrim(@town)+'</town>')
INSERT @xmlfile VALUES ('      <county>'+rtrim(@county)+'</county>')
INSERT @xmlfile VALUES ('      <postcode>'+rtrim(@postcode)+'</postcode>')
INSERT @xmlfile VALUES ('      <propertytype>'+rtrim(@propertytype)+'</propertytype>')
INSERT @xmlfile VALUES ('      <registrationtype>'+rtrim(@registrationtype)+'</registrationtype>')
INSERT @xmlfile VALUES ('      <bedrooms>'+convert(varchar,@bedrooms)+'</bedrooms>')
INSERT @xmlfile VALUES ('      <garage>'+rtrim(@garage)+'</garage>')
INSERT @xmlfile VALUES ('      <garden>'+rtrim(@garden)+'</garden>')
INSERT @xmlfile VALUES ('      <description>'+rtrim(LEFT(@description,1000))+'</description>')
INSERT @xmlfile VALUES ('   </registration>')

END

CLOSE xml_cursor
DEALLOCATE xml_cursor

INSERT @xmlfile VALUES ('</RegistrationList>')

SELECT XMLrecord from @xmlfile order by ID

END

GO

-- right, to use that all we have to do is :

usp_xml_registrations

-- or more correctly exec usp_xml_registrations

-- now to get the darn thing out on disk as a file.
-- you will need to change the paths and DBNAME accordingly
-- you might also need to turn on xp_cmdshell using sp_configure

EXEC master..xp_cmdshell 'bcp "exec MY_DB..usp_xml_registrations" queryout "D:\xml_Export_Files\xml_bspc_2009_mw_longhand.xml" -T -c -CACP'

-- and that as they say is that - the most manual way or "rolling your own"

0
Author Commented:
Mark,

Cant thank you enough for your help with this.

OK - the situation I am at is that I now have your 'usp_xml_registrations' in place and returning results, however when i try to run:

'EXEC master..xp_cmdshell 'bcp "exec bspc_database..usp_xml_registrations" queryout "D:\xml_Export_Files\xml_bspc_2009_mw_longhand.xml" -T -c -CACP'

I get the results as per the snippet below?

Thanks again

Grant

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors]            [-f formatfile]          [-e errfile]
[-F firstrow]             [-L lastrow]             [-b batchsize]
[-n native type]          [-c character type]      [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier]  [-t field terminator]    [-r row terminator]
[-i inputfile]            [-o outfile]             [-a packetsize]
[-T trusted connection]   [-v version]             [-R regional enable]
[-k keep null values]     [-E keep identity values]
[-h "load hints"]         [-x generate xml format file]
NULL

0
That normally means it cannot resolve the BCP command... or it thinks it is missing needed arguments. Normally gives an indication on the very first line before throwing that message.

If you have multiple SQL servers for example, you will need -Sservername\instance parameter.

This is the command exactly how I used it in a query window, and happen to be working in my default server:

EXEC master..xp_cmdshell 'bcp "exec mrwtemp..usp_xml_registrations" queryout "C:\ee\xml_Export_Files\xml_bspc_2009_mw_longhand.xml" -T -c -CACP'

Now, replacing the database name and the export location, you command looks like it should be OK...

Sometimes, especially in a batch then it is often easier to set up the BCP command seperately :

declare @bcp varchar(500)
set @bcp = 'bcp "exec mrwtemp..usp_xml_registrations" queryout "C:\ee\xml_Export_Files\xml_bspc_2009_mw_longhand.xml" -T -c -CACP'

EXEC master..xp_cmdshell @bcp

need to be careful with quoted identifiers if using directly.

Check the location, did it create a zero byte file ? copy and paste the exec part of the command and run it interactively...

Sometimes it is easier to use a BCP command that you know should work, and then work backwards from that... like :

exec master..xp_cmdshell 'bcp "select * from master..spt_values" queryout "c:\test.txt" -T -c'

Another thing that can happen is (especially in SQL2008, sometimes in 2005) it complains about "BCP host-files must contain at least one column" and that is easy enough, simply change the procedure into another function and then do the select (see below)...

You can use the function instead of the procedure anyway, and then becomes a more "traditional" select type statement... See below

-- rather than a procedure, it can also be expressed as a function

create function udf_xml_registrations (@params varchar(20))
returns @xmlfile table (xmlrecord varchar (1000),id int identity	)
AS
BEGIN

--    SET NOCOUNT ON

--    DECLARE @xmlfile table (xmlrecord varchar (1000),id int identity	)

DECLARE @RegistrationID varchar(20)
DECLARE @town varchar(100)
DECLARE @county varchar(100)
DECLARE @postcode varchar(100)
DECLARE @propertytype varchar(100)
DECLARE @registrationtype varchar(100)
DECLARE @bedrooms int
DECLARE @garage varchar(100)
DECLARE @garden varchar(100)
DECLARE @description varchar(max)

INSERT @xmlfile VALUES ('<RegistrationList>')

OPEN xml_cursor

WHILE @@FETCH_STATUS = 0
BEGIN

--		SET @description = replace(@description,'&','&amp;')
--		SET @description = replace(@description,'"','&quot;')
--		SET @description = replace(@description,'<','&lt;')
--		SET @description = replace(@description,'>','&gt;')

SELECT @description = dbo.udf_prepare_xml(@description)

INSERT @xmlfile VALUES ('   <registration RegistrationID="'+rtrim(@registrationid)+'">')
INSERT @xmlfile VALUES ('      <town>'+rtrim(@town)+'</town>')
INSERT @xmlfile VALUES ('      <county>'+rtrim(@county)+'</county>')
INSERT @xmlfile VALUES ('      <postcode>'+rtrim(@postcode)+'</postcode>')
INSERT @xmlfile VALUES ('      <propertytype>'+rtrim(@propertytype)+'</propertytype>')
INSERT @xmlfile VALUES ('      <registrationtype>'+rtrim(@registrationtype)+'</registrationtype>')
INSERT @xmlfile VALUES ('      <bedrooms>'+convert(varchar,@bedrooms)+'</bedrooms>')
INSERT @xmlfile VALUES ('      <garage>'+rtrim(@garage)+'</garage>')
INSERT @xmlfile VALUES ('      <garden>'+rtrim(@garden)+'</garden>')
INSERT @xmlfile VALUES ('      <description>'+rtrim(LEFT(@description,1000))+'</description>')
INSERT @xmlfile VALUES ('   </registration>')

END

CLOSE xml_cursor
DEALLOCATE xml_cursor

INSERT @xmlfile VALUES ('</RegistrationList>')

--    SELECT XMLrecord from @xmlfile order by ID
RETURN

END

GO

-- now test using a select statement

select xmlrecord from mrwtemp.dbo.udf_xml_registrations (NULL) order by ID

-- and then try the BCP

EXEC master..xp_cmdshell 'bcp "select xmlrecord from mrwtemp.dbo.udf_xml_registrations (NULL) order by ID" queryout "C:\ee\xml_Export_Files\xml_bspc_2009_mw_longhand.xml" -T -c -CACP'

0
Author Commented:
Mark,

I now have the function - 'udf_xml_registrations' in place and if i use the 'select xmlrecord from bspc_database.dbo.udf_xml_registrations (NULL) order by ID'  the query returns the expected results. however when i use 'EXEC master..xp_cmdshell 'bcp "select xmlrecord from bspc_database.dbo.udf_xml_registrations
(NULL) order by ID" queryout "D:\xml_Export_Files\xml_bspc_2009_mw_longhand.xml" -T -c -CACP'

I am getting the same rsults as posted earlier in the snippet.

Thanks

Grant
0
OK, did you try the other "little" bcp command ?

There is a leading single quote on what you are pasting, can you show me exactly how you are using it ? The command itself looks OK, and do you get any other messages ? There is normally a first line before it comes up with that "block" of correct usage...

0
Author Commented:
Mark,

The exact syntax I am using is as follows:
EXEC master..xp_cmdshell
'bcp "select xmlrecord from bspc_database.dbo.udf_xml_registrations
(NULL) order by ID" query out
"D:\xml_Export_Files\xml_bspc_2009_mw_longhand.xml" -T -c -CACP'

Line 1 from the output is as follows:
usage: bcp {dbtable | query} {in | out | queryout | format} datafile

Thanks for your patience with this.

Grant
0
needs to be on the one line I think... and queryout is one word...

EXEC master..xp_cmdshell 'bcp "select xmlrecord from bspc_database.dbo.udf_xml_registrations (NULL) order by ID" queryout "D:\xml_Export_Files\xml_bspc_2009_mw_longhand.xml" -T -c -CACP'

0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Mark,

Worked perfectly when syntax used on one line.

Thanks so much for you help

Grant
0