Solved

Storing XML in Text Field in Table

Posted on 2004-09-13
7
243 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

920 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

18 Experts available now in Live!

Get 1:1 Help Now