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...

When you extract elements you should use the element name (e.g. columnid[1]).

The way you wrote the code you're trying to query attributes.

Here are both versions for comparison and to show...

Instead of

WHERE Date BETWEEN '1/1/2009' and '12/31/2009 23:59:59.997'

I'd rather use

WHERE Date >= '20090101' and Date<'20100101'

The main reason for date formatting YMD instead of M/D/Y is to have no...