You have probably found that although you can specify the namespace to use for serializing to xml with FOR XML, there is no option for specifying the namespace of a generated xmlschema. Instead you get an autoincrementing uri for the users session, e.g. urn:schemas-microsoft-com:sql:SqlRowSet7
You can however assign that generated schema to an xml variable, and then simply use an xml dml to replace the value of the targetNamespace attribute. Here is a simple example, enjoy:
declare @x xml, @ns varchar(1024)
set @ns=’newNamespace’
set @x = (
select *
from (
select top 0 1 as intId
) as t for xml auto, xmlschema
)
set @x.modify(‘declare namespace xsd = “http://www.w3.org/2001/XMLSchema“;
replace value of /xsd:schema[1]/@targetNamespace with sql:variable(“@ns”)’)
select @x
Leave a comment