Solved

SQL XML Output

Posted on 2009-07-16
25
922 Views
Last Modified: 2012-05-07
Dear Experts,

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

sp_makewebtask @outputfile = 'D:\XML_Export_Files\xml_bspc_2009.xml',
@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>
  <Address1>Digby House</Address1>
  <Address2>Main Street</Address2>
  <Town>Chirnside</Town>
  <County></County>
  <PostCode>TD11 3UE</PostCode>
  <PropertyType>Detached</PropertyType>
  <RegistrationType>Sale</RegistrationType>
  <AskingPriceType>Offers Over</AskingPriceType>
  <AskingPrice>290000</AskingPrice>
  <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">
<Address1>1 Some Street</Address1>
 <Town>Some Town</Town>
etc etc

Can anyone advise on how I can achieve this.

Thanks for any help




0
Comment
Question by:grantballantyne
  • 12
  • 8
  • 5
25 Comments
 
LVL 18

Expert Comment

by:brejk
Comment Utility
I guess you should use FOR XML PATH clause in your T-SQL query:

SELECT
  RegistrationID AS [@RegistrationID],
  Address1,
  Address2,
  ...
FROM
  xmloutput
FOR XML PATH('Registration')
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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

 

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''), TYPE, ELEMENTS',

@templatefile = 'D:\XML_Template_File\bspcxml.tpl'

Open in new window

0
 
LVL 18

Expert Comment

by:brejk
Comment Utility
@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
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
 
LVL 18

Expert Comment

by:brejk
Comment Utility
@mark_wills: Isn't it the same piece of code I've written above? ;-)
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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 Comment

by:grantballantyne
Comment Utility
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:

sp_makewebtask @outputfile = 'D:\XML_Export_Files\xml_bspc_2009.xml',
@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
 
LVL 18

Expert Comment

by:brejk
Comment Utility
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
 
LVL 18

Expert Comment

by:brejk
Comment Utility
@grantballantyne: Mark has already provided a solution for your issue - double the quotes aroud the "Registration" word and the code will run.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
@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'

Open in new window

0
 

Author Comment

by:grantballantyne
Comment Utility
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
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:grantballantyne
Comment Utility
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:
'sp_makewebtask @outputfile = 'D:\XML_Export_Files\xml_bspc_2009_tst.xml',
@query = 'SELECT registrationid [@RegistrationID], address1, address2,
town,county,postcode,propertytype,registrationtype,askingpricetype,askingprice,
bedrooms,garage,garden,description,thumblink,imagelink,detaillink
 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
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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 Comment

by:grantballantyne
Comment Utility
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
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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',

      address1 varchar(100),

      address2 varchar(100),

      town varchar(100),

      county varchar(100),

      postcode varchar(100),

      propertytype varchar(100),

      registrationtype varchar(100),

      askingpricetype varchar(100),

      askingprice money,

      bedrooms int,

      garage varchar(100),

      garden varchar(100),

      description varchar(max),

      thumblink varchar(100),

      imagelink varchar(100),

      detaillink varchar(100))
 

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 @address1 varchar(100)

    DECLARE @address2 varchar(100)

    DECLARE @town varchar(100)

    DECLARE @county varchar(100)

    DECLARE @postcode varchar(100)

    DECLARE @propertytype varchar(100)

    DECLARE @registrationtype varchar(100)

    DECLARE @askingpricetype varchar(100)

    DECLARE @askingprice money

    DECLARE @bedrooms int

    DECLARE @garage varchar(100)

    DECLARE @garden varchar(100)

    DECLARE @description varchar(max)

    DECLARE @thumblink varchar(100)

    DECLARE @imagelink varchar(100)

    DECLARE @detaillink varchar(100)
 

	DECLARE xml_cursor CURSOR FAST_FORWARD FOR SELECT RegistrationID,address1,address2,town,county,postcode,propertytype,registrationtype,askingpricetype,askingprice,bedrooms,garage,garden,description,thumblink,imagelink,detaillink from EE_XML_REGISTRATIONS
 

	INSERT @xmlfile VALUES ('<RegistrationList>')
 

	OPEN xml_cursor

	FETCH NEXT FROM xml_cursor INTO @RegistrationID,@address1,@address2,@town,@county,@postcode,@propertytype,@registrationtype,@askingpricetype,@askingprice,@bedrooms,@garage,@garden,@description,@thumblink,@imagelink,@detaillink 
 

	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)

		SELECT @address1    = dbo.udf_prepare_xml(@address1)

		SELECT @address2    = dbo.udf_prepare_xml(@address2)

		SELECT @detaillink  = dbo.udf_prepare_xml(@detaillink)
 

		INSERT @xmlfile VALUES ('   <registration RegistrationID="'+rtrim(@registrationid)+'">')

		INSERT @xmlfile VALUES ('      <address1>'+rtrim(@address1)+'</address1>')

		INSERT @xmlfile VALUES ('      <address2>'+rtrim(@address2)+'</address2>')

		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 ('      <askingpricetype>'+rtrim(@askingpricetype)+'</askingpricetype>')

		INSERT @xmlfile VALUES ('      <askingprice>'+convert(varchar,@askingprice)+'</askingprice>')

		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 ('      <thumblink>'+rtrim(@thumblink)+'</thumblink>')

		INSERT @xmlfile VALUES ('      <imagelink>'+rtrim(@imagelink)+'</imagelink>')

		INSERT @xmlfile VALUES ('      <detaillink>'+rtrim(@detaillink)+'</detaillink>')

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

    	FETCH NEXT FROM xml_cursor INTO @RegistrationID,@address1,@address2,@town,@county,@postcode,@propertytype,@registrationtype,@askingpricetype,@askingprice,@bedrooms,@garage,@garden,@description,@thumblink,@imagelink,@detaillink 
 

	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'
 

-- the above uses windows authentication, could also use -Uusername -Ppassword
 

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

Open in new window

0
 

Author Comment

by:grantballantyne
Comment Utility
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]

  [-S server name]          [-U username]            [-P password]

  [-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

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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 @address1 varchar(100)

    DECLARE @address2 varchar(100)

    DECLARE @town varchar(100)

    DECLARE @county varchar(100)

    DECLARE @postcode varchar(100)

    DECLARE @propertytype varchar(100)

    DECLARE @registrationtype varchar(100)

    DECLARE @askingpricetype varchar(100)

    DECLARE @askingprice money

    DECLARE @bedrooms int

    DECLARE @garage varchar(100)

    DECLARE @garden varchar(100)

    DECLARE @description varchar(max)

    DECLARE @thumblink varchar(100)

    DECLARE @imagelink varchar(100)

    DECLARE @detaillink varchar(100)
 

	DECLARE xml_cursor CURSOR FAST_FORWARD FOR SELECT RegistrationID,address1,address2,town,county,postcode,propertytype,registrationtype,askingpricetype,askingprice,bedrooms,garage,garden,description,thumblink,imagelink,detaillink from EE_XML_REGISTRATIONS
 

	INSERT @xmlfile VALUES ('<RegistrationList>')
 

	OPEN xml_cursor

	FETCH NEXT FROM xml_cursor INTO @RegistrationID,@address1,@address2,@town,@county,@postcode,@propertytype,@registrationtype,@askingpricetype,@askingprice,@bedrooms,@garage,@garden,@description,@thumblink,@imagelink,@detaillink 
 

	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)

		SELECT @address1    = dbo.udf_prepare_xml(@address1)

		SELECT @address2    = dbo.udf_prepare_xml(@address2)

		SELECT @detaillink  = dbo.udf_prepare_xml(@detaillink)
 

		INSERT @xmlfile VALUES ('   <registration RegistrationID="'+rtrim(@registrationid)+'">')

		INSERT @xmlfile VALUES ('      <address1>'+rtrim(@address1)+'</address1>')

		INSERT @xmlfile VALUES ('      <address2>'+rtrim(@address2)+'</address2>')

		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 ('      <askingpricetype>'+rtrim(@askingpricetype)+'</askingpricetype>')

		INSERT @xmlfile VALUES ('      <askingprice>'+convert(varchar,@askingprice)+'</askingprice>')

		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 ('      <thumblink>'+rtrim(@thumblink)+'</thumblink>')

		INSERT @xmlfile VALUES ('      <imagelink>'+rtrim(@imagelink)+'</imagelink>')

		INSERT @xmlfile VALUES ('      <detaillink>'+rtrim(@detaillink)+'</detaillink>')

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

    	FETCH NEXT FROM xml_cursor INTO @RegistrationID,@address1,@address2,@town,@county,@postcode,@propertytype,@registrationtype,@askingpricetype,@askingprice,@bedrooms,@garage,@garden,@description,@thumblink,@imagelink,@detaillink 
 

	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'

Open in new window

0
 

Author Comment

by:grantballantyne
Comment Utility
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
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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 Comment

by:grantballantyne
Comment Utility
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
Comment Utility
needs to be on the one line I think... and queryout is one word...

Please copy and paste into your query window exactly :




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'

Open in new window

0
 

Author Comment

by:grantballantyne
Comment Utility
Mark,

Worked perfectly when syntax used on one line.

Thanks so much for you help

Grant
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
No problems and happy to be of some help.

0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now