Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Modify xml with namespaces

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 ?

like image 739
spdro Avatar asked Oct 19 '12 11:10

spdro


People also ask

Can XML have multiple namespaces?

When you use multiple namespaces in an XML document, you can define one namespace as the default namespace to create a cleaner looking document. The default namespace is declared in the root element and applies to all unqualified elements in the document. Default namespaces apply to elements only, not to attributes.

What is the purpose of namespace in XML?

An XML namespace is a collection of names that can be used as element or attribute names in an XML document. The namespace qualifies element names uniquely on the Web in order to avoid conflicts between elements with the same name.


1 Answers

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:

  1. We insert everything from @enumeration (with the unnecessary root) somewhere into the other xml
  2. We copy the desired content to the right place
  3. 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;
like image 107
TheConstructor Avatar answered Nov 02 '22 23:11

TheConstructor