Solved

Storing XML in Text Field in Table

Posted on 2004-09-13
7
272 Views
Last Modified: 2008-03-03
I have query that results in a long string of XML.  I'm trying to save that xml to the a text field of a table.  I get the error 'incorrect syntax near xml'.

I've since learned that you can't use the update command with a text field and am in the process of designing a cursor which will use the updatetext function to update the field.

However,  Before doing this I did a little test where I changed the field type to varchar(8000).  I still get the same error.

The query looks like this...


Update myxmltable
Set xmlField = (Select * From Clients For XML Auto, Elements)

1) Why does the above not work when xmlField is varchar(8000)
2) Will my idea to create a cursor and use updatetext work?
0
Comment
Question by:billy21
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 12043041
The problem is that you can't use the output of a SELECT ... FOR XML statement in a subquery.
See 'FOR XML Clause' in BOL

Guidelines for Using the FOR XML Clause
- The FOR XML clause is valid only in the SELECT statement and is subject to these limitations:
- FOR XML is not valid in subselections, whether it is in UPDATE, INSERT, or DELETE statements, a nested SELECT statement, or other statements (SELECT INTO, assignment).
- FOR XML is not valid in a SELECT statement used in a view definition or in a user-defined function that returns a rowset.
- FOR XML cannot be used in a selection that requires further processing in a stored procedure.
- FOR XML cannot be used with cursors.
- Generally, FOR XML cannot be used for any selections that do not produce direct output to the Microsoft® SQL Server™ 2000 client.
- FOR XML cannot be used in a stored procedure when called in an INSERT statement.

0
 
LVL 6

Author Comment

by:billy21
ID: 12043051
Is there any way at all to save my xml to a text field in a table?
0
 
LVL 6

Author Comment

by:billy21
ID: 12043061
Can I for instance, save the xml data to a variable?
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 26

Accepted Solution

by:
Hilaire earned 500 total points
ID: 12043080
Another problem with FOR XML is that any application using ODBC will return results split in chunks of 2033 characters.
Most of the time the only workaround is to use an ADO Stream on the front-end side to get the stream into a file.

If you need to stick to T-SQL, here's a link to a post where I gave a full T-SQL Solution to get the output of a SELECT ... FOR XML statement into a T-SQL varchar variable.

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21056538.html

HTH

Hilaire
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12043219
So you can save output to a variable but no to a text variable AFAIK, cause SQL Server won't let you create a TEXT/NTEXT variable.

I think it's possible to get the whole string in a TEXT column using several loops
- get ado stream size
- read it by chunks of 4000 characters
- use WRITETEXT/UPDATETEXT in a loop till you reach full size
0
 
LVL 6

Author Comment

by:billy21
ID: 12043230
SQL Server will let you create text and ntext variables so long as you declare them as parameters

ie...

Create Procedure MyProc(@MyTextField Text)
AS

---
Go
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12043343
You're absolutely right.
I never managed to use this feature in a useful way (of course that doesn't mean it's not possible !) ...
You're welcome if you have cool code samples
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

630 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