SQL Server remove namespaces default

Question

I am using Xquery on sql 2008 server and the results contain namespace prefixes (example p1 :) that are generated automatically. I am using a default namespace.

How to remove them?

Example / flow:

1-

SET @ROOMLIST='
          <RoomList>
           <Root xmlns="http://zzz.yyy.com" >
             <RoomInfo>
                <AdultNum>2</AdultNum>
                <ChildNum>2</ChildNum>
                <ChildAges>
                   <ChildAge age="1"/>
                  <ChildAge age="12"/>
                </ChildAges>
             </RoomInfo>
          </Root>
      </RoomList>'

2- SQL query:

declare @rt table (roomno int, Can_Adl int, Can_Chd int, ChildAges xml)
;WITH XMLNAMESPACES(DEFAULT 'http://zzz.yyy.com')
    insert into @rt (Can_Adl,Can_Chd,ChildAges)
    select  r.value('(AdultNum/text())[1]','int') as CAN_ADL,
            r.value('(ChildNum/text())[1]','int') as CAN_CHD,
            r.query('ChildAges') as ChildAges

    from @ROOMLIST.nodes('//RoomInfo') as t(r)
    outer apply (select t.r.query('ChildAges') as c) qlo

3- Result:

<p1:ChildAges xmlns:p1="http://zzz.yyy.com">
   <p1:ChildAge age="1" />
   <p1:ChildAge age="12" />
</p1:ChildAges>

I want to get:

<ChildAges xmlns="http://zzz.yyy.com">
   <ChildAge age="1" />
   <ChildAge age="12" />
</ChildAges>

Why would you care? This is exactly the same as a default namespace. It is like asking you want the result `1+2`, but you got `2+1`instead (it doesn't matter). This seems to be a XY problem, so maybe it would be more helpful if you would explain why you care about this and how you want to use this result? — dirkk, Jan 23 '18 at 22:21
It is important because I want to use the result in stored procedure and functions in SQL. In the code space I add example. — npalle, Jan 24 '18 at 03:00
I already got this. My point, though, is that your result and your expected result is exactly the same. Why would your following stored procedures or whatever you do care how it is formatted? Sounds to me like you are doing e.g. regex based "parsing" of XML, which is just terrible (and incorrect) — dirkk, Jan 24 '18 at 09:10

score 2 · Accepted Answer · answered Jan 24 '18 at 16:48

I got the solution:

   r.query('declare default element namespace 
   "http://zzz.yyy.com";.') as ChildAges

SQL query:

   ;WITH XMLNAMESPACES(DEFAULT 'http://zzz.yyy.com' )
    insert into @rt (Can_Adl,Can_Chd,ChildAges)

    select  r.value('(//AdultNum)[1]','int') as Can_Adl,
            r.value('(//ChildNum)[1]','int') as Can_Chd,
            r.query('declare default element 
            namespace"http://zzz.yyy.com";.') as ChildAges
    from @ROOMLIST.nodes('//ChildAges') as t(r)

SQL Server remove namespaces default

1 Answers1