Putting attribute on root node using FOR XML PATH & With XML NameSpaces

**nsd1026** · Feb 28 '12, 11:28 PM

Hi, I'm new to the Sql. I have a query in which i need to include version = '3.0' attribute in the root node before the namespaces starts or end of the namespaces but should be in the root. Can any one help me Please.

this is my Query

Code:

WITH XMLNAMESPACES (DEFAULT 'http://www.w3.org/2002/06/xhtml2', 
 'http://www.w3.org/2001/XMLSchema-instance' AS xsi,
 'http://technerd.co.nz/forms/delete' as del,
 'http://technerd.co.nz/forms/eer' as eer) 
SELECT 'http://technerd.co.nz/forms/delete/delete.xsd' "@xsi:schemaLocation", 
 '' AS "@eer:ComponentID" ,
 'abcdef' AS 'eer:X' ,
 NULL AS 'eer:Y' 
 FOR XML PATH('del:Delete'), ELEMENTS XSINIL
;

here is my result

Code:

<del:Delete xmlns:eer="http://technerd.co.nz/forms/eer" 
 xmlns:del="http://technerd.co.nz/forms/delete" 
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 xmlns="http://www.w3.org/2002/06/xhtml2" 
 xsi:schemaLocation="http://technerd.co.nz/forms/delete/delete.xsd" 
 eer:ComponentID="">
 <eer:X>abcdef</eer:X>
 <eer:Y xsi:nil="true" />
</del:Delete>

My expected result should be like this

Code:

<del:Delete xmlns:eer="http://technerd.co.nz/forms/eer" 
 xmlns:del="http://technerd.co.nz/forms/delete" 
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 xmlns="http://www.w3.org/2002/06/xhtml2" 
 xsi:schemaLocation="http://technerd.co.nz/forms/delete/delete.xsd" 
 eer:ComponentID="" 
 Version = "3.0">
 <eer:X>abcdef</eer:X>
 <eer:Y xsi:nil="true" />
</del:Delete>

Thanks in Advance,
Swetha.