Hello! There is a following template:

<PersonalProgramList> <PersonalProgram> <One>randomInfo</One> <Two>randomInfo</Two> </PersonalProgram> <PersonalProgram> <One>randomInfo</One> <Two>randomInfo</Two> </PersonalProgram> <PersonalProgram> <One>randomInfo</One> <Two>randomInfo</Two> </PersonalProgram> </PersonalProgramList>

How, using T-SQL to turn it into:

  <tns:PersonalProgramList xmlns:tns="http://example.com/blalba/"> <PersonalProgram xmlns="http://example.com/blalba/"> <One>randomInfo</One> <Two>randomInfo</Two> </PersonalProgram> <PersonalProgram xmlns="http://example.com/blalba/"> <One>randomInfo</One> <Two>randomInfo</Two> </PersonalProgram> <PersonalProgram xmlns="http://example.com/blalba/"> <One>randomInfo</One> <Two>randomInfo</Two> </PersonalProgram> </tns:PersonalProgramList>

PersonalProgramList - root, PersonalProgram - unlimited number.

  • @ i-one, will not go. - Knack
  • Inside a PersonalProgram can only be what is in question (only One and Two with text inside), or whatever? - i-one
  • I would like to see your request, which you receive such XML. Or do you have the source data contain XML, you only need to add a namespace? - minamoto
  • @ i-one, anything, even an unlimited number of blocks nested into each other. - Knack
  • one
    You can see here the option with the replace. - Alexander Petrov

2 answers 2

A simple way to replace an element's namespace (with XQuery tools) is unknown to me.

If the original XML schema were exactly the same as in the question (there can only be One and Two with some text inside), then you could honestly rebuild the original XML in the correct format using FLWOR :

 declare @xml xml = N'<PersonalProgramList>...'; select @xml.query(N' <tns:PersonalProgramList xmlns:tns="http://example.com/blalba/"> { for $pp in /PersonalProgramList/PersonalProgram return <PersonalProgram xmlns="http://example.com/blalba/"> { for $a in $pp/* return if (local-name($a) = "One") then <One>{$a/text()}</One> else <Two>{$a/text()}</Two> } </PersonalProgram> } </tns:PersonalProgramList>');

If anything can be inside a PersonalProgram , then you can also rebuild the XML using FLWOR, specifying the desired namespace for the PersonalProgram , and copying everything inside. However, when replacing the namespace for a PersonalProgram , everything inside will clearly indicate xmlns="" , which, except with the help of REPLACE I do not see an option to remove (therefore I don’t really like this way):

 select cast(replace(cast(@xml.query(N' <tns:PersonalProgramList xmlns:tns="http://example.com/blalba/"> { for $pp in /PersonalProgramList/PersonalProgram return <PersonalProgram xmlns="http://example.com/blalba/"> { $pp/* } </PersonalProgram> } </tns:PersonalProgramList>') as nvarchar(max)), N'xmlns=""', N'') as xml);
  • And how do you consider the option to replace the necessary tags without FLWOR? XML in VARCHAR (MAX) and the replacement of <PersonalProgram> by <PersonalProgram xmlns="http://example.com/blalba/"> , but then you will have to wrap this back in REPLACE to change the root. - Knack
  • one
    @Knack, if inside a PersonalProgram can not be nested PersonalProgram and PersonalProgramList , then yes - you can get by with a few REPLACE . You also need to consider whether there can be empty <PersonalProgram/> . - i-one
  • nested <PersonalProgram> and empty <PersonalProgram/> cannot be. Thanks for answers. - Knack

I can offer you the option of adding a default namespace to the root level.

Suppose you have this table structure:

 create table PersonalProgram (One nvarchar(max), Two nvarchar(max)); insert into PersonalProgram values ('randomIfo', 'randomIfo');

Then the request will be:

 WITH XMLNAMESPACES ('http://example.com/blalba/' as tns, default 'http://example.com/blalba/') select * from PersonalProgram for xml path('PersonalProgram'), root('tns:PersonalProgramList')

If you basically defaltovy namespace add not to the root level, then it will be a little more difficult option, now offhand is not ready to answer.