XQuery insert new node

  • kiril.lazarov.77

    Hall of Fame

    Points: 3742

    Hi,

    I want to insert a new node but not sure what the best way to do this.

    DECLARE @XML XML = '<Attribute>

    <A1>6529</A1>

    <A2>6529</A2>

    </Attribute>';

    DECLARE @n VARCHAR(100) = 'A3';

    DECLARE @v VARCHAR(100) = 'New value';

    --expected output

    DECLARE @XML XML = '<Attribute>

    <A1>6529</A1>

    <A2>6529</A2>

    <A3>New value</A3>

    </Attribute>';

    Both the name and value are stored procedure parameters, so cannot be hardcoded. Also, the attributes are ordered (sequence specified in XSD) so the position should be determined automatically where the node should be inserted (for instance, ... (/Attribute)[1] will fail for <A3> as it fails validation). Please help. Thank you.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    Quick'n simple example

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @TXML XML = '<Attribute>

    <A1>6529</A1>

    <A2>6529</A2>

    </Attribute>';

    DECLARE @NNODE VARCHAR(100) = 'A3';

    DECLARE @nval VARCHAR(100) = 'New value';

    DECLARE @Nt VARCHAR(200) = '<{{@NNODE}}>{{@NVAL}}</{{@NNODE}}>';

    DECLARE @NXML XML;

    SELECT @NXML = CONVERT(XML,REPLACE(REPLACE(@NT,'{{@NNODE}}',@NNODE),'{{@NVAL}}',@NVAL),0);

    SET @TXML.modify('insert sql:variable("@NXML") as last into (/Attribute)[1]');

    SELECT @TXML ;

    Results

    <Attribute>

    <A1>6529</A1>

    <A2>6529</A2>

    <A3>New value</A3>

    </Attribute>

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    Edit: deleted the post

  • kiril.lazarov.77

    Hall of Fame

    Points: 3742

    This is not the first time you are trying to help me out. Thank you very much.

    I should have mentioned that my XML is validated against a schema that looks something like this:

    CREATE XML SCHEMA COLLECTION MySchema AS N'<?xml version="1.0"?>

    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">

    <xs:element name="Attribute">

    <xs:complexType>

    <xs:sequence>

    <xs:element name="A1" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>

    <xs:element name="A2" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>

    <xs:element name="A3" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>

    <xs:element name="A4" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>

    </xs:sequence>

    </xs:complexType>

    </xs:element>

    </xs:schema>

    ';

    Now, if we run the query:

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @TXML XML(MySchema) = '<Attribute>

    <A1>6529</A1>

    <A2>6529</A2>

    <A4>6529</A4>

    </Attribute>';

    DECLARE @NNODE VARCHAR(100) = 'A3';

    DECLARE @nval VARCHAR(100) = 'New value';

    DECLARE @Nt VARCHAR(200) = '<{{@NNODE}}>{{@NVAL}}</{{@NNODE}}>';

    DECLARE @NXML XML;

    SELECT @NXML = CONVERT(XML,REPLACE(REPLACE(@NT,'{{@NNODE}}',@NNODE),'{{@NVAL}}',@NVAL),0);

    SET @TXML.modify('insert sql:variable("@NXML") as last into (/Attribute)[1]');

    SELECT @TXML ;

    This will result in an error message which is expected due to the sequence:

    XML Validation: Unexpected element(s): A3. Location: /*:Attribute[1]/*:A3[1]

    What I'm looking for is query that inserts A3 in between A2 and A4.

    Also, nodes are optional as you can see in the schema, so

    if we had: '<Attribute><A4>6529</A4></Attribute>'

    I'd expect A3 to be inserted before A4

    if we had: '<Attribute><A1>6529</A1></Attribute>'

    I'd expect A3 to be inserted after A1

    Hope this makes sense? Thank you again!!!!

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    The XML modify/insert has 4 positioning directives, as first,as last, after and before, changing from as last to after for the correct positioning, note the slight difference in the syntax.

    Often the XML must be interrogated prior to modification.

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @TXML XML = '<Attribute>

    <A1>6529</A1>

    <A2>6529</A2>

    </Attribute>';

    DECLARE @NNODE VARCHAR(100) = 'A3';

    DECLARE @nval VARCHAR(100) = 'New value';

    DECLARE @Nt VARCHAR(200) = '<{{@NNODE}}>{{@NVAL}}</{{@NNODE}}>';

    DECLARE @NXML XML;

    SELECT @NXML = CONVERT(XML,REPLACE(REPLACE(@NT,'{{@NNODE}}',@NNODE),'{{@NVAL}}',@NVAL),0);

    SET @TXML.modify('insert sql:variable("@NXML") after (/Attribute/A2)[1]');

    SELECT @TXML ;

  • kiril.lazarov.77

    Hall of Fame

    Points: 3742

    Thank you.

    Is there a way to replace A2 with something so that it is not hard-coded?

    SET @TXML.modify('insert sql:variable("@NXML") after (/Attribute/A2)[1]');

    No guarantees that A2 node will exist as they are optional. In this case A3 would go after A1 (or before A4). Producing:

    A1

    A3

    A4

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    As I mentioned before, often the XML must be interrogated to determine the placement of the inserted node. This sample checks both the XML to be modified and the XML Schema Collection to correctly place the node insert.

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    GO

    CREATE XML SCHEMA COLLECTION MySchema AS N'<?xml version="1.0"?>

    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">

    <xs:element name="Attribute">

    <xs:complexType>

    <xs:sequence>

    <xs:element name="A1" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>

    <xs:element name="A2" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>

    <xs:element name="A3" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>

    <xs:element name="A4" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>

    </xs:sequence>

    </xs:complexType>

    </xs:element>

    </xs:schema>

    ';

    GO

    DECLARE @TXML XML = '<Attribute>

    <A1>6700</A1>

    <A4>6730</A4>

    </Attribute>';

    DECLARE @XMLSCHEMA_NAME NVARCHAR(128) = N'MySchema'

    DECLARE @NNODE VARCHAR(100) = 'A3';

    DECLARE @nval VARCHAR(100) = 'New value';

    DECLARE @Nt VARCHAR(200) = '<{{@NNODE}}>{{@NVAL}}</{{@NNODE}}>';

    DECLARE @NXML XML;

    DECLARE @XSTNODE VARCHAR(100) = '';

    ;WITH XML_SCHEMA_STRUCTURE AS

    (

    SELECT

    XSC.name

    ,XCP.placement_id

    FROM sys.xml_schema_collections XC

    INNER JOIN sys.xml_schema_components XSC

    ON XC.xml_collection_id = XSC.xml_collection_id

    INNER JOIN sys.xml_schema_component_placements XCP

    ON XSC.xml_component_id = XCP.placed_xml_component_id

    WHERE XC.name = @XMLSCHEMA_NAME

    AND XSC.name IS NOT NULL

    )

    ,XML_STRUCTURE AS

    (

    SELECT

    TNODE.DATA.value('local-name(.)','VARCHAR(100)') AS XNODE

    FROM @TXML.nodes('/Attribute/*') AS TNODE(DATA)

    )

    ,NODE_TO_INSERT AS

    (

    SELECT

    XSS.name

    ,XSS.placement_id

    FROM XML_SCHEMA_STRUCTURE XSS

    WHERE XSS.name = @NNODE

    )

    SELECT

    @XSTNODE = (SELECT TOP(1) XSS.name

    FROM XML_SCHEMA_STRUCTURE XSS

    LEFT OUTER JOIN XML_STRUCTURE XS

    ON XSS.name = XS.XNODE

    WHERE XSS.placement_id < (SELECT TOP(1) placement_id

    FROM NODE_TO_INSERT NTI)

    AND XS.XNODE IS NOT NULL

    ORDER BY XSS.placement_id DESC);

    SELECT @NXML = CONVERT(XML,REPLACE(REPLACE(@NT,'{{@NNODE}}',@NNODE),'{{@NVAL}}',@NVAL),0);

    IF (@XSTNODE IS NOT NULL)

    BEGIN

    SET @TXML.modify('insert sql:variable("@NXML") after (/Attribute/*[local-name() = sql:variable("@XSTNODE")])[1]');

    END

    IF (@XSTNODE IS NULL)

    BEGIN

    SET @TXML.modify('insert sql:variable("@NXML") as first into (/Attribute)[1]');

    END

    SELECT @TXML;

    GO

    DROP XML SCHEMA COLLECTION MySchema;

    GO

    Results

    <Attribute>

    <A1>6700</A1>

    <A3>New value</A3>

    <A4>6730</A4>

    </Attribute>

  • Arthur Olcot

    SSCertifiable

    Points: 6008

    Here is a slightly different take on the suggestion by Eirikur that achieves the same but without querying the system views. I've also opted to use dynamic sql to generate the additional xml section rather than a search/replace. This ensures that non-xml safe characters in the node value are escaped properly but you need to assess the risk of using dynamic SQL and if you trust what is going to be invoking the logic.

    DECLARE @XML XML = '<Attribute>

    <A1>1111</A1>

    <A2>2222</A2>

    <A4>4444</A4>

    </Attribute>';

    DECLARE @n VARCHAR(100) = 'A3';

    DECLARE @v VARCHAR(100) = 'New value';

    DECLARE @XmlToInsert XML

    DECLARE @SQL NVARCHAR(200)

    SET @SQL = N'SELECT @XmlToInsert = (SELECT @v FOR XML PATH(''' + @n + '''))'

    EXEC sys.sp_executesql @SQL, N'@XmlToInsert XML OUTPUT, @v VARCHAR(100)'

    , @XmlToInsert = @XmlToInsert OUTPUT

    , @v = @v

    IF @XML.value('count(/Attribute/node())', 'int') > 0

    SET @XML.modify('insert sql:variable("@XmlToInsert") before (/Attribute/node()[local-name(.) > sql:variable("@n") ])[1]')

    ELSE

    SET @XML.modify('insert sql:variable("@XmlToInsert") as first into (/Attribute)[1]')

    SELECT @XML

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    Good catch with the node value Arthur, don't think it merits a dynamic sql though as one can easily encode the value with a simple FOR XML statement.

    😎

    /* Simple encoding statement using FOR XML

    */

    SELECT @nval = (

    SELECT

    '' + @nval

    FOR XML PATH('')

    );

    The reason for querying the xml schema collection views is simply the fact that one cannot rely on the alphabetical order of the node names in a sequence, hence the schema definition is the only reliable source. The query sample is very simple though, real life applications are normally quite complicated and often it is simpler to have either a table or a hard coded representation.

    This code sample has a sequence of non alphabetical order and a node value containing "illegal" characters.

    USE tempdb;

    GO

    SET NOCOUNT ON;

    GO

    CREATE XML SCHEMA COLLECTION MySchema AS N'<?xml version="1.0"?>

    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">

    <xs:element name="Attribute">

    <xs:complexType>

    <xs:sequence>

    <xs:element name="A4" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>

    <xs:element name="A1" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>

    <xs:element name="A2" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>

    <xs:element name="A3" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>

    </xs:sequence>

    </xs:complexType>

    </xs:element>

    </xs:schema>

    ';

    GO

    DECLARE @TXML XML = '<Attribute>

    <A4>6700</A4>

    <A1>6730</A1>

    </Attribute>';

    DECLARE @XMLSCHEMA_NAME NVARCHAR(128) = N'MySchema'

    DECLARE @NNODE VARCHAR(100) = 'A3';

    DECLARE @nval VARCHAR(100) = 'New </>?;:value';

    DECLARE @Nt VARCHAR(200) = '<{{@NNODE}}>{{@NVAL}}</{{@NNODE}}>';

    DECLARE @NXML XML;

    DECLARE @XSTNODE VARCHAR(100) = '';

    ;WITH XML_SCHEMA_STRUCTURE AS

    (

    SELECT

    XSC.name

    ,XCP.placement_id

    FROM sys.xml_schema_collections XC

    INNER JOIN sys.xml_schema_components XSC

    ON XC.xml_collection_id = XSC.xml_collection_id

    INNER JOIN sys.xml_schema_component_placements XCP

    ON XSC.xml_component_id = XCP.placed_xml_component_id

    WHERE XC.name = @XMLSCHEMA_NAME

    AND XSC.name IS NOT NULL

    )

    ,XML_STRUCTURE AS

    (

    SELECT

    TNODE.DATA.value('local-name(.)','VARCHAR(100)') AS XNODE

    FROM @TXML.nodes('/Attribute/*') AS TNODE(DATA)

    )

    ,NODE_TO_INSERT AS

    (

    SELECT

    XSS.name

    ,XSS.placement_id

    FROM XML_SCHEMA_STRUCTURE XSS

    WHERE XSS.name = @NNODE

    )

    SELECT

    @XSTNODE = (SELECT TOP(1) XSS.name

    FROM XML_SCHEMA_STRUCTURE XSS

    LEFT OUTER JOIN XML_STRUCTURE XS

    ON XSS.name = XS.XNODE

    WHERE XSS.placement_id < (SELECT TOP(1) placement_id

    FROM NODE_TO_INSERT NTI)

    AND XS.XNODE IS NOT NULL

    ORDER BY XSS.placement_id DESC);

    /* Simple encoding statement using a

    CTE and FOR XML

    */

    ;WITH NVALUE_STR AS

    (

    SELECT @nval AS XSTR

    )

    SELECT @nval = (

    SELECT

    '' + NV.XSTR

    FROM NVALUE_STR NV

    FOR XML PATH('')

    );

    SELECT @NXML = CONVERT(XML,REPLACE(REPLACE(@NT,'{{@NNODE}}',@NNODE),'{{@NVAL}}',@NVAL),0);

    IF (@XSTNODE IS NOT NULL)

    BEGIN

    SET @TXML.modify('insert sql:variable("@NXML") after (/Attribute/*[local-name() = sql:variable("@XSTNODE")])[1]');

    END

    IF (@XSTNODE IS NULL)

    BEGIN

    SET @TXML.modify('insert sql:variable("@NXML") as first into (/Attribute)[1]');

    END

    SELECT @TXML;

    GO

    DROP XML SCHEMA COLLECTION MySchema;

    GO

    Results

    <Attribute>

    <A4>6700</A4>

    <A1>6730</A1>

    <A3>New </>?;:value</A3>

    </Attribute>

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply