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
Solved

Storing XML in Text Field in Table

Posted on 2004-09-13
7
257 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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 …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

828 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