 |
|
| Computers Forum Index » Computer - Databases - MS SQL Server » change xml stored in text column... |
|
Page 1 of 1 |
|
| Author |
Message |
| migurus... |
 Posted: Fri Oct 23, 2009 1:48 am |
|
|
|
Guest
|
SQL 2005, the table definition:
CREATE TABLE [dbo].[T1] (
[ID] [int] NOT NULL,
[CONF_XML] [text] NULL
)
The CONF_XML column holds a content of the xml config file. Actual xml
files are not stored in the file system. The content of this column is
huge, so I am not showing it here.
I am need to update a value in the xml in all records.
Example:
<XGroup name="mygroup" type="generic" level="1">
<X label="0" name="LOC" type="str" value="\\192.168.1.1\LOGS\LVL0"/>
</XGroup>
I need to change into
<X label="0" name="LOC" type="str" value="\\192.168.2.3\LOGS\LVL4"/>
Any pointers will be appreciated. Just a note - I saw many .NET based
examples of dealing with xml and sql on the net, but I don't know .NET
and have no time to learn it now. |
|
|
| Back to top |
|
|
|
| migurus... |
| Posted: Fri Oct 23, 2009 5:12 pm |
|
|
|
Guest
|
On Oct 22, 6:48 pm, migurus <migu... at (no spam) yahoo.com> wrote:
Quote: SQL 2005, the table definition:
CREATE TABLE [dbo].[T1] (
[ID] [int] NOT NULL,
[CONF_XML] [text] NULL
)
The CONF_XML column holds a content of the xml config file. Actual xml
files are not stored in the file system. The content of this column is
huge, so I am not showing it here.
I am need to update a value in the xml in all records.
Example:
XGroup name="mygroup" type="generic" level="1"
X label="0" name="LOC" type="str" value="\\192.168.1.1\LOGS\LVL0"/
/XGroup
I need to change into
X label="0" name="LOC" type="str" value="\\192.168.2.3\LOGS\LVL4"/
Any pointers will be appreciated. Just a note - I saw many .NET based
examples of dealing with xml and sql on the net, but I don't know .NET
and have no time to learn it now.
What I tried so far:
bcp OUT, change info in the file and bcp IN that file. I use sed and
very careful not to damage the file while changing it. The problem is,
the bcp IN dies in the middle with messgae
"Unexpected EOF in data file"
I think this is due to the fact that the total lenght of the text is
bigger after my change, but my bcp format file says:
8.0
3
1 SQLINT 0 4 "|" 1
ID ""
2 SQLCHAR 0 0 "|" 3
CONFIG_XML SQL_Latin1_General_CP1_CI_AS
and it keeps the string lenght in bin format in the file, which I am
not adjusting.
Again, any pointers would be appreciated. |
|
|
| Back to top |
|
|
|
| migurus... |
| Posted: Fri Oct 23, 2009 6:37 pm |
|
|
|
Guest
|
On Oct 23, 10:12 am, migurus <migu... at (no spam) yahoo.com> wrote:
Quote: On Oct 22, 6:48 pm, migurus <migu... at (no spam) yahoo.com> wrote:
SQL 2005, the table definition:
CREATE TABLE [dbo].[T1] (
[ID] [int] NOT NULL,
[CONF_XML] [text] NULL
)
The CONF_XML column holds a content of the xml config file. Actual xml
files are not stored in the file system. The content of this column is
huge, so I am not showing it here.
I am need to update a value in the xml in all records.
Example:
XGroup name="mygroup" type="generic" level="1"
X label="0" name="LOC" type="str" value="\\192.168.1.1\LOGS\LVL0"/
/XGroup
I need to change into
X label="0" name="LOC" type="str" value="\\192.168.2.3\LOGS\LVL4"/
Any pointers will be appreciated. Just a note - I saw many .NET based
examples of dealing with xml and sql on the net, but I don't know .NET
and have no time to learn it now.
What I tried so far:
bcp OUT, change info in the file and bcp IN that file. I use sed and
very careful not to damage the file while changing it. The problem is,
the bcp IN dies in the middle with messgae
"Unexpected EOF in data file"
I think this is due to the fact that the total lenght of the text is
bigger after my change, but my bcp format file says:
8.0
3
1 SQLINT 0 4 "|" 1
ID ""
2 SQLCHAR 0 0 "|" 3
CONFIG_XML SQL_Latin1_General_CP1_CI_AS
and it keeps the string lenght in bin format in the file, which I am
not adjusting.
Again, any pointers would be appreciated.- Hide quoted text -
- Show quoted text -
OK, I found the solution that is adequate for my case,
just for the benefit of future searches:
http://sqlserver2000.databases.aspfaq.com/how-do-i-handle-replace-within-an-ntext-column-in-sql-server.html
Author shows how to manipulate TEXT column, using TEXTPTR, PATINDEX
and UPDATETEXT |
|
|
| Back to top |
|
|
|
| Erland Sommarskog... |
| Posted: Sat Oct 24, 2009 8:46 am |
|
|
|
Guest
|
migurus (migurus at (no spam) yahoo.com) writes:
But that is unnecessarily painful if you are on SQL 2005.
My first reaction when I saw your post was that you should use the xml
data type to store your data and then use the xml type method .modify
method. But as I started to compose an example, I found that was about
as complicated using TEXTPTR & co. Since .modify can only operate on a
single mode in the document, you need to slice it into fragments first
and then reassemble them.
However, you can convert to varchar(MAX), and it's straigtforward:
CREATE TABLE [dbo].[T1] (
[ID] [int] NOT NULL,
[CONF_XML] [text] NULL
)
go
insert T1(ID, CONF_XML)
VALUES (1, '<XGroup name="mygroup" type="generic" level="1">
<X label="0" name="LOC" type="str" value="\\192.168.1.1\LOGS\LVL0"/>
<X label="2" name="STDF" type="str" value="\\192.168.1.1\LOGS\LVL7"/>
</XGroup>')
go
update T1
SET CONF_XML = replace(convert(varchar(MAX), CONF_XML),
'192.168.1.1', '192.168.2.3')
go
select * from T1
go
drop table T1
But say that you that the IP address had appeared in several different
places in the XML document. In this case, .modify would have been a
better choice.
Finally, the text data type is deprecated and with good reason. Use
varchar(MAX) instead. Or xml in cases like this one.
--
Erland Sommarskog, SQL Server MVP, esquel at (no spam) sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Sun Dec 06, 2009 12:35 am
|
|