Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

XML xsi:noNamespaceSchemaLocation

Posted on 2010-08-27
9
Medium Priority
?
3,442 Views
Last Modified: 2012-05-10
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\Some.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:noNamespaceSchemaLocation="\\SomeServer\SomeFile\Some.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:noNamespaceSchemaLocation="\\SomeServer\SomeFile\Some.xsd">
  <Path>Some SQL Code </Path>
</Root>

Basically, I need the xmlns:noNamespaceSchemaLocation to be xsi:noNamespaceSchemaLocation

I am new to the XML world so any assistance would be appreciated.

Thanks.

-JAllen

0
Comment
Question by:jallen_gomedico
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33554763
jallen_gomedico,

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.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33554778
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
0
 

Author Comment

by:jallen_gomedico
ID: 33590563
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:noNamespaceSchemaLocation but I would like to see some sql on how this is acheived...

Thanks.

-JAllen
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33590665
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.

0
 

Author Comment

by:jallen_gomedico
ID: 33590689
Thanks!
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 33593550
Nope... Even when specifically telling it to with the XSINIL declaration...

From BOL : The XML namespace prefix xsi cannot be redeclared when the ELEMENTS XSINIL directive is being used on the query.

But there are some "tricks" depending on how complex your query really is...


WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' AS xsi)
SELECT '\\SomeServer\SomeFile\Some.xsd' "@xsi:noNamespaceSchemaLocation",  
  'Some SQL Code' AS Path
FOR XML PATH('Root'), ELEMENTS XSINIL
;


0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33594588
Thanks for joining in, Mark!
0
 

Author Comment

by:jallen_gomedico
ID: 33598530
Thanks Mark, and you too mwvisa1!

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33602101
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:noNamespaceSchemaLocation="\\SomeServer\SomeFile\Some.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,productnumber from production.product for xml path('Product'))

DECLARE @myXML XML
set @myXML = '<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="\\SomeServer\SomeFile\Some.xsd">
   <PATH> </PATH>
</root>'

SET @myXML.modify('insert sql:variable("@newElements") into (/root/PATH)[1]')

-- hope that helps just a bit more...  
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question