如何使用sql从xmlns：ns中删除：ns

I want to create a XML in SQL like this

我想像这样在SQL中创建XML

<Root xmlns="http://www.example.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.example.com http://www.example.com /media/XSD/123.xsd">
  <Header>
    <Node1>Test</Node1>
  </Header>
</Root>

For that I have used the code

为此,我使用了代码

declare @xml xml
;with xmlnamespaces ('http://www.w3.org/2001/XMLSchema-instance' as xsi,  'http://www.example.com ' as ns)
select 
    @xml = ((SELECT 'Test' as Node1   
             FOR XML PATH('Header'), ROOT('Root')));

set @xml.modify('insert(attribute xsi:schemaLocation {"http://www.example.com  http://www.example.com /media/XSD/123.xsd"}) into (/Root)[1]')                               
select @xml

But the output is like this:

但输出是这样的:

<Root xmlns:ns="http://www.example.com " xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.example.com  http://www.example.com /media/XSD/123.xsd">
  <Header>
    <Node1>Test</Node1>
  </Header>
</Root>

How can I remove :ns from xmlns:ns?

如何从xmlns:ns中删除:ns?

Thanks for your help

谢谢你的帮助

1 个解决方案

#1

You need to use this XML namespace as the default namespace (instead of specifying a ns prefix):

您需要使用此XML命名空间作为默认命名空间(而不是指定ns前缀):

DECLARE @xml XML

;WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi,
                    DEFAULT 'http://www.example.com')
SELECT
    @xml = ((SELECT 'Test' as Node1   
             FOR XML PATH('Header'), ROOT('Root')));

SET @xml.modify('insert(attribute xsi:schemaLocation {"http://www.example.com  http://www.example.com /media/XSD/123.xsd"}) into (/Root)[1]');

This gives you the desired output of:

这为您提供了所需的输出:

<Root xmlns="http://www.example.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Header>
    <Node1>Test</Node1>
  </Header>
</Root>

#1