Try this:

DECLARE @t TABLE (

Acno VARCHAR(20),

acdesc VARCHAR(20),

actype CHAR(1)

)

INSERT INTO @t (acno, acdesc, actype)

SELECT '1122334', 'Tesr acct', 'C' UNION ALL

SELECT '0004455', 'Bank Test Account', 'S'

SELECT

'12345' AS Business_ID,

(

SELECT

acno AS ACCOUNT_NUMBER,

acdesc AS...

I may be able to help only if I can take a look at the 'real' XML and 'real' code. If the XML does not have any sensitive data, you...

Hmm..Just ran it and it worked well.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)

Table '#3E52440B'. Scan count 1,...

Can you also post a sample XML document? I see you posted something, but the code-formatting must have eaten that. Try putting it between and blocks.

Is this an INLINE Function? Can you post the create script (generate it using SSMS) of this function?

Two quick suggestions that I have are the following: (1) Try using TYPED XML (2) Try using XML Indexes.

In SQL Server, you cannot specify XSD file to create a TYPED XML document, instead you need to create an XML SCHEMA Collection and then associate an XML document with...

I think the performance is bad here for two reasons: loops and UNTYPED XML. In most cases, loops give bad performance. Since the XML document being processed is not TYPED...

When you restore a FILESTREAM enabled database, you can specify the location where the filestream data container(s) should be placed. Just like you specify the MDF and LDF file locations,...

Thank you Paul, Jason and Brad. I am glad to know that you liked it.

I would suggest take a look at SQL Server Performance Dashboard Reports, which can display all those graphs - io, cpu, memory - and a bunch of other information right...

you are right.

Here is an example that generates CDATA and namespace declarations.

SELECT

1 AS Tag,

NULL AS Parent,

'Jacob' AS 'Person!1!Name!element',

'Hellow world' AS 'Person!1!greeting!CDATA',

'http://beyondrelational.com/xml.aspx'

AS 'Person!1!xmlns:x'

FOR XML EXPLICIT

You can use WITH XMLNAMESPACES() to generate namespace information along with the generated XML. You can use FOR XML EXPLICIT to generate CDATA sections.

Which version of SQL Server are you using? If you are on SQL Server 2005 or above, you can use WITH XMLNAMESPACES() to generate an XML document with namespace information.

if...

I agree with John. The UNIQUEIDENTIFIER column should be unique by itself.