Modify xml with namespaces

Question

I'm trying to modify predefined xml (xsd to be more specific) in TSQL. I want to insert enumeration restriction to one of the xsd elements.

The task is to fill xsd restrictions based on a query example:

create table #list(value nvarchar(100))
insert into #list values('item 1')
insert into #list values('item 2')
insert into #list values('item 3')
insert into #list values('item 4')
insert into #list values('item 5')
insert into #list values('item 6')

declare @enumeration as xml
;with xmlnamespaces('http://www.w3.org/2001/XMLSchema' as xs)
select @enumeration = (
    select value as '@value'
    from #list for xml path('xs:enumeration')
)

declare @schema xml
set @schema =
'<xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="test">
    <xs:element name="test" msdata:IsDataSet="true" msdata:MainDataTable="Example" msdata:UseCurrentLocale="true">
        <xs:complexType>
            <xs:choice maxOccurs="unbounded">
                <xs:element name="Example">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="myList" minOccurs="1" nillable="false">
                                <xs:simpleType>
                                    <xs:restriction base="xs:string">
                                        <xs:maxLength value="50" />
                                    </xs:restriction>
                                </xs:simpleType>
                            </xs:element>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:choice>
        </xs:complexType>
    </xs:element>
</xs:schema>'

set @schema.modify
    ('insert sql:variable("@enumeration")
    into (//xs:element[@name=''myList'']/xs:simpleType/xs:restriction)[1]')

select @schema

The problem is that code outputs unnecessary xmlns attribute

<xs:enumeration xmlns:xs="http://www.w3.org/2001/XMLSchema" value="item 1" />

Can anyone help ?

Good luck. Remember: it's often faster to train yourself to live with unnecessary but harmless namespace declarations than to make a system stop emitting them. — C. M. Sperberg-McQueen, Oct 19 '12 at 23:56
Not exactly your case, but you can find the answer [here](http://social.msdn.microsoft.com/Forums/en/transactsql/thread/2f7bdfbf-8e40-456b-84e8-195318649703). In other words: if you can't ignore it and xml->nvarchar(max)->replace->xml is not elegant enough, you can always write a CLR ;) — OzrenTkalcecKrznaric, Oct 26 '12 at 21:47
@АртёмЦарионов perfectly? not but probably I can :) I spent most of my life near slavic populations, maybe i can pronunce his name correctly :P — fnurglewitz, Dec 11 '12 at 13:32
possible duplicate of [How do I remove redundant namespace in nested query when using FOR XML PATH](http://stackoverflow.com/questions/3242070/how-do-i-remove-redundant-namespace-in-nested-query-when-using-for-xml-path) — Muqo, Jul 23 '15 at 18:20
It's been 10 years with no action but perhaps a few more votes will eventually give us an actual fix rather than the workarounds people keep trying to find: http://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml-statements — bielawski, Jul 07 '17 at 20:39

TheConstructor · Answer 1 · 2017-01-27T15:09:50.933

It got a bit more ugly than I hoped. Mainly because I can't use sql:variable("@enumeration")/delete-me/node() inside a .modify().

I assume, that you can modify the generation of the @enumeration, as follows:

CREATE TABLE #list (value nvarchar(100));
INSERT  INTO #list
VALUES  ('item 1');
INSERT  INTO #list
VALUES  ('item 2');
INSERT  INTO #list
VALUES  ('item 3');
INSERT  INTO #list
VALUES  ('item 4');
INSERT  INTO #list
VALUES  ('item 5');
INSERT  INTO #list
VALUES  ('item 6');

DECLARE @enumeration AS xml;
WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema' AS xs)
SELECT @enumeration = (
    SELECT value AS '@value'
    FROM #list FOR XML PATH('xs:enumeration'), ROOT('delete-me'), TYPE
);

The idea is to use FOR XML with ROOT, so that the generated namespace is at the unnecessary root element (which can be skipped). Otherwise we would have to recreate the xs:enumeration-elements later.

Two solutions

Use `.modify()` three times

The idea:

We insert everything from @enumeration (with the unnecessary root) somewhere into the other xml
We copy the desired content to the right place
We delete the no longer needed copy of @enumeration in @schema.

DECLARE @schema xml;
SET @schema = '<xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="test">
    <xs:element name="test" msdata:IsDataSet="true" msdata:MainDataTable="Example" msdata:UseCurrentLocale="true">
        <xs:complexType>
            <xs:choice maxOccurs="unbounded">
                <xs:element name="Example">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="myList" minOccurs="1" nillable="false">
                                <xs:simpleType>
                                    <xs:restriction base="xs:string">
                                        <xs:maxLength value="50" />
                                    </xs:restriction>
                                </xs:simpleType>
                            </xs:element>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:choice>
        </xs:complexType>
    </xs:element>
</xs:schema>';

SET @schema.modify('insert sql:variable("@enumeration")
    into /');

SET @schema.modify('declare namespace xs="http://www.w3.org/2001/XMLSchema";
insert /delete-me/node()
    into (//xs:element[@name=''myList'']/xs:simpleType/xs:restriction)[1]');

SET @schema.modify('delete /delete-me');

SELECT  @schema;

DROP TABLE #list;

`.query()` to create `@schema`

If you can change the part where @schema is created, you can generate @schema directly as .query() from @enumeration:

DECLARE @enumeration AS xml;
WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema' AS xs)
SELECT @enumeration = (
    SELECT value AS '@value'
    FROM #list FOR XML PATH('xs:enumeration'), ROOT('delete-me'), TYPE
);

DECLARE @schema xml;
SET @schema = @enumeration.query('<xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="test">
    <xs:element name="test" msdata:IsDataSet="true" msdata:MainDataTable="Example" msdata:UseCurrentLocale="true">
        <xs:complexType>
            <xs:choice maxOccurs="unbounded">
                <xs:element name="Example">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="myList" minOccurs="1" nillable="false">
                                <xs:simpleType>
                                    <xs:restriction base="xs:string">
                                        <xs:maxLength value="50" />
                                        {/delete-me/node()}
                                    </xs:restriction>
                                </xs:simpleType>
                            </xs:element>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:choice>
        </xs:complexType>
    </xs:element>
</xs:schema>');

SELECT  @schema;

score 0 · Answer 2 · answered Dec 10 '12 at 23:28

0

You can do a simple string replace if the xml isn't too big:

DECLARE @schemaVARCHAR (MAX)
SET @schemaVARCHAR = CAST(@schema AS VARCHAR(MAX))
SET @schemaVARCHAR = 
    REPLACE(
        @schemaVARCHAR, 
        '<xs:enumeration xmlns:xs="http://www.w3.org/2001/XMLSchema" value="item 1" />',
        ''
    )

SET @schema = CAST(@schemaVARCHAR AS XML)

answered Dec 10 '12 at 23:28

Russell Fox

5,273
1
24
28

Then put that in the REPLACE function. Mine was just an example. You can also do this repeatedly if you need to remove/change multiple things. – Russell Fox Dec 12 '12 at 19:23
The point is that you shouldn't edit XML like it's text. The prefix doesn't matter. – John Saunders Dec 12 '12 at 19:41
@JohnSaunders I disagree - the answer has merit. If the completely harmless xml bloat created in this very specific instance is totally unacceptable to the OP then doing a string replace operation is warranted in the interests of sanity. I agree fully with you that treating xml as text is evil. – tom redfern Apr 03 '13 at 08:53
@hugh: you've just shown why it is without merit: treating XML as text is evil. To make it more clear: what if a different prefix is used _for each element_? – John Saunders Apr 03 '13 at 09:39

score 0 · Answer 3 · answered Apr 23 '13 at 15:09

0

If your XML isn't too complicated, why not use FOR XML EXPLICIT instead?

Another workaround would be to get generate the XML without the namespace and then add it in afterwards.

answered Apr 23 '13 at 15:09

KaraokeStu

758
7
17

Modify xml with namespaces

3 Answers3

Two solutions

Use .modify() three times

.query() to create @schema

Use `.modify()` three times

`.query()` to create `@schema`