SQL Server and XML

• andbean

  Newbie

  Points: 9

  More actions

  June 16, 2005 at 8:25 am

  #102594

  I have to generate an XMl from a SQL Server 2000 table. Using the clause FOR XML i can only send the result to screen or to file.

  I want, instead, store the result, into a field column in another table. There's a way i can do it without passing through a file (and OpenXML)?

  tnks in advance

   

  andrea

• Lowell

  SSC Guru

  Points: 323546

  More actions

  June 16, 2005 at 10:02 am

  #566175

  in sql 2000, the for xml clause cannot be captured into any variable, table or other server object. Generally, FOR XML cannot be used for any selections that do not produce direct output to the Microsoft® SQL Server™ 2000 client. I think it is because the output is built similar to a TEXT or IMAGE field and streamed to the ODBC driver or dataadapter, and you cannot manipulate the stream.

  here's the authoritative link to a microsft whitepaper on what you can and cannot do;

  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsql/ac_openxml_0alh.asp

  about the only thing i can think is to dimp it to a file, and then read it back so you can manipulate it, but that is exactly what you wanted to avoid. sql 2005 has much better xml support, of course.

  maybe you could write your own procedure, which returns a varchar(8000) or preferably  a TEXT field, which parses results you requested into an XML format; maybe there is already such a  function in the contributions here on ssc:

  Scripts	Rating	Search Score
  Prepare an XML document from one stored in a table	3	100
  sp_xml_PrepareDocumentFromColumn	4.14	100
  XML Encode UDF	2.11	100
  Process XML against XSL template and save output	4	100
  Generate MetaData XML Document	4.5	99
  DTS package to read status of all jobs	4.09	99
  Splits a delimited string into a table	4.25	99
  Search All Database Objects for a string	4	99
  Make HTTP posts / request from within SQL	3.67	99
  SP_HELPTEXT Modified for XML	4.29	99
  Selecting XML out	1.5	99

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!