Solved

Storing XML in Text Field in Table

Posted on 2004-09-13
7
268 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

728 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