jallen_gomedico
asked on
XML xsi:noNamespaceSchemaLocation
I am trying to use a stored procedure in SQL Server 2005 to create a xml file for a document management system. The code I am using is:
WITH XMLNAMESPACES (
'\\SomeServer\SomeFile\Som e.xsd'
as noNamespaceSchemaLocation,
'http://www.w3.org/2001/XMLSchema-instance' as xsi
)
Select 'Some SQL Code '
FOR XML PATH ('Path'), ELEMENTS, TYPE, ROOT('Root')
The Results I get are:
<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:noNamespaceSchemaLoc ation="\\S omeServer\ SomeFile\S ome.xsd">
<Path>Some SQL Code </Path>
</Root>
The Results I would like to get are:
<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocat ion="\\Som eServer\So meFile\Som e.xsd">
<Path>Some SQL Code </Path>
</Root>
Basically, I need the xmlns:noNamespaceSchemaLoc ation to be xsi:noNamespaceSchemaLocat ion
I am new to the XML world so any assistance would be appreciated.
Thanks.
-JAllen
WITH XMLNAMESPACES (
'\\SomeServer\SomeFile\Som
as noNamespaceSchemaLocation,
'http://www.w3.org/2001/XMLSchema-instance' as xsi
)
Select 'Some SQL Code '
FOR XML PATH ('Path'), ELEMENTS, TYPE, ROOT('Root')
The Results I get are:
<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:noNamespaceSchemaLoc
<Path>Some SQL Code </Path>
</Root>
The Results I would like to get are:
<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocat
<Path>Some SQL Code </Path>
</Root>
Basically, I need the xmlns:noNamespaceSchemaLoc
I am new to the XML world so any assistance would be appreciated.
Thanks.
-JAllen
This might be a better reference for you as it is the BOL from Microsoft specifically regarding SQL's WITH XMLNAMESPACES versus the other was just to exemplify for you how xmlns is used.
http://msdn.microsoft.com/en-us/library/ms177400.aspx
http://msdn.microsoft.com/en-us/library/ms177400.aspx
ASKER
mwvisa1,
Thanks for the reponses. Although the links help in the understanding of the namespaces, they didn't really help with my issue. I see plenty of examples on various forums of xml with xsi:noNamespaceSchemaLocat ion but I would like to see some sql on how this is acheived...
Thanks.
-JAllen
Thanks for the reponses. Although the links help in the understanding of the namespaces, they didn't really help with my issue. I see plenty of examples on various forums of xml with xsi:noNamespaceSchemaLocat
Thanks.
-JAllen
Yeah, I believe I know what you are trying to do, but don't believe that is possible with xmlnamepaces; however, I will call some other experts in SQL to see if they know how to work around this.
ASKER
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for joining in, Mark!
ASKER
Thanks Mark, and you too mwvisa1!
No problems, and thanks to mwvisa1 for getting in touch :)
Just as a small "bonus" for more complex queries you can resort to something like (usinf dynamic SQL so you can more easily construct the list of elements):
declare @my_xml_insert nvarchar(max)
set @my_xml_insert = '
DECLARE @myXML XML
set @myXML = ''<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocat ion="\\Som eServer\So meFile\Som e.xsd">
<PATH> </PATH>
</root>''
'
set @my_xml_insert = @my_xml_insert + 'SET @myXML.modify(''
insert (
<Element1>1</Element1>,
<Element2>3</Element2>
)
into (/root/PATH)[1] '')
select @myXML'
exec (@my_xml_insert)
-- and in 2008 then you can list those added elements by using the insert sql:variable construct and get rid of the dynamic sql :
-- First we need to build an appropriate variable to use for the insert, so, @newelements is something like :
DECLARE @newElements xml
set @newelements = (select ProductID,name,productnumb er from production.product for xml path('Product'))
DECLARE @myXML XML
set @myXML = '<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocat ion="\\Som eServer\So meFile\Som e.xsd">
<PATH> </PATH>
</root>'
SET @myXML.modify('insert sql:variable("@newElements ") into (/root/PATH)[1]')
-- hope that helps just a bit more...
Just as a small "bonus" for more complex queries you can resort to something like (usinf dynamic SQL so you can more easily construct the list of elements):
declare @my_xml_insert nvarchar(max)
set @my_xml_insert = '
DECLARE @myXML XML
set @myXML = ''<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocat
<PATH> </PATH>
</root>''
'
set @my_xml_insert = @my_xml_insert + 'SET @myXML.modify(''
insert (
<Element1>1</Element1>,
<Element2>3</Element2>
)
into (/root/PATH)[1] '')
select @myXML'
exec (@my_xml_insert)
-- and in 2008 then you can list those added elements by using the insert sql:variable construct and get rid of the dynamic sql :
-- First we need to build an appropriate variable to use for the insert, so, @newelements is something like :
DECLARE @newElements xml
set @newelements = (select ProductID,name,productnumb
DECLARE @myXML XML
set @myXML = '<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocat
<PATH> </PATH>
</root>'
SET @myXML.modify('insert sql:variable("@newElements
-- hope that helps just a bit more...
xmlns: is what appears before all namespace prefixes; therefore, if you are wanting a namespace prefix of noNamespaceSchemaLocation then xmlns: should proceed it and not xsi.
http://www.w3schools.com/xml/xml_namespaces.asp
Please clarify what your intent is, so can figure out if I am missing your point here. Otherwise, you could want to specify namespace for elements that don't have a prefix and that would be xmlns with no :{prefix} after it.