SQL Server 2005 XQuery insert into namespace problem

[blondebier]

Hello Peeps,

Sorry about the subject description: I'll try and explain...

I've got a stored procedure in SQL Server 2005 and I need to use the new XQuery funtionality to modify an XML document. I am trying to insert some new nodes into an existing document that has a namespace.

I create my new node like this:

SET @companyNode = convert(xml, '<company>' + Rtrim(@Company) + '</company>')

I then go to insert this into my XML document using:

SET @MergeXML = CONVERT(xml, (CONVERT(nvarchar(MAX), @CurrentXML) + CONVERT(nvarchar(MAX), @companyNode)))
SET @MergeXML.modify('declare namespace ccns="urnrganisation.uk.com/wsi"; insert /*[2] as last into (//ccns:credentials)[1]')
SET @MergeXML.modify('delete /*[2]')
SET @CurrentXML = @MergeXML

It does the insert but it creates it like this:

<credentials xmlns="urnrganisation.uk.com/wsi">
<company xmlns="">Monster Wheels</company>
</credentials>

Why does it create an empty namespace on the <company> element?

I don't want this to happen I need it to do the insert into the namespace of the document and not put this in the company element.

Any ideas what I should be doing?

Cheers.

 

[blondebier]

Sorted it!

SET @credentialsNode = CONVERT(xml, '<temp xmlns="urnrganisation.uk.com/wsi">' + (CONVERT(nvarchar(MAX), @companyNode)) + '</temp>')

SET @MergeXML = CONVERT(xml, (CONVERT(nvarchar(MAX), @CurrentXML) + CONVERT(nvarchar(MAX), @credentialsNode)))
SET @MergeXML.modify('declare namespace ccns="urnrganisation.uk.com/wsi"; insert //ccns:temp/* as first into (//ccns:credentials)[1]')
SET @MergeXML.modify('delete /*[2]')
SET @BureauXML = @MergeXML

I hope that helps...