• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

Storing XML in Text Field in Table

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
billy21
Asked:
billy21
  • 4
  • 3
1 Solution
 
HilaireCommented:
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
 
billy21Author Commented:
Is there any way at all to save my xml to a text field in a table?
0
 
billy21Author Commented:
Can I for instance, save the xml data to a variable?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
HilaireCommented:
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
 
HilaireCommented:
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
 
billy21Author Commented:
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
 
HilaireCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now