Hard to tell what you're trying to do...

Please provide more details.

Especially the following phrase is unclear (at least to me...): "without using txt file"

Please provide more details. The best waa would be to post ready to use sample data (including table def, sample data and expected result). For details please see the first...

What would be the expected result for the (very limited) sample data?

I would use a table to store the character value and the substitute.

Step two would be the replacement by calling the REPLACE function for each entry in the table and...

You could use the CASE statement. Something like:

DECLARE @ordercol sysname,@sql AS VARCHAR(500)

SET @ordercol='object_id'

SELECT TOP 5 *

FROM sys.objects

ORDER BY

CASE @ordercol

WHEN 'object_id' THEN STR(OBJECT_ID)

WHEN 'name' THEN name

ELSE CONVERT(CHAR(10),create_date,120)

END

It's important to...

duplicate post. Please continue duscussion here.

duplicate post. Please continue duscussion here.

Glad you found a solution that works for you and thank you for posting back.

Glad I could help 😀

As far as I can see, my first code snippet and the view will return the same data. What's different?

Based on your sample data: what would be your expected result?

I don't really understand why you'd need a view containing just one row...

but the following statement worked fine on my system (after changing the temp table to a permanet table,...

change your SELECT part to the following code:

DECLARE @soapHeader VARCHAR(1000)

DECLARE @soapFooter VARCHAR(1000)

SET @soapHeader='<SOAP-ENV:Envelope

xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"

SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">

<SOAP-ENV:Header>

<ns1:AuthenticationInfo xmlns:ns1="urn:thisNamespace">

<ns1:UserName>John Doe</ns1:UserName>

</ns1:AuthenticationInfo>

</SOAP-ENV:Header>

<SOAP-ENV:Body>

'

--soap footer:

SET @soapFooter=' </SOAP-ENV:Body>

</SOAP-ENV:Envelope>'

SELECT CAST(@soapHeader+(SELECT

[AddressLine1] AS [Address/Address1]

, [AddressLine2] AS [Address/Address2]

, [City] AS [City]

FROM

#Address

FOR...

Would you please provide some ready to use sample data as described in the first link in my signature?

Based on your (rather vague) sample data I'd simply use

c.value('FERTILIZER_CHARGE[1]', 'VARCHAR(30)')

but the...

Since you're using a typed xml document you need to use XML NAMESPACES to qualify your elements (even though the namespace is nowhere used inside the xml document).

Since I couldn't...