Replace root element from XML database field

Alen Cappelletti 891 Reputation points
2020-12-02T16:58:48.013+00:00

HI all,
as usual I try to improve my low level skills on XML query.

Problem: from XML field into DB I need to replace root tag element.
Below the codes and the questions:

FROM CODE....

DECLARE @xml as xml
SET @xml = N'
<enfinity  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xml="http://www.w3.org/XML/1998/namespace" major="6" minor="1" family="enfinity" branch="enterprise" build="7.4.5.1.46">
  <product sku="101234">
    <sku>101234</sku>
    <long-description xml:lang="en-US">Morazol</long-description>
    <long-description xml:lang="it-IT">Morazol</long-description>
    <name xml:lang="en-US">Zeolithe A1</name>
    <name xml:lang="it-IT">Zeolithe </name>
    <short-description xml:lang="en-US">Zeolithe A1</short-description>
    <short-description xml:lang="it-IT">Zeolithe A1</short-description>
    <product-type name="Basic" domain="system" />
    <template>product/GeneralProductInformationInclude</template>
    <sharing-groups>
      <sharing-group id="B2C_IT_GROUP" organization="Pharmaton" />
    </sharing-groups>
    <online>0</online>
    <manufacturer>
      <manufacturer-name>ZeoDiv</manufacturer-name>
      <manufacturer-sku>ZeoDiv</manufacturer-sku>
    </manufacturer>
    <category-links>
      <category-link name="CAT_LEV3_89" domain="ProdottiFarma" default="1" hotdeal="0" />
    </category-links>
    <custom-attributes>
      <custom-attribute name="GTINs" dt:dt="string">
        <value>0000000000001</value>
        <value>0000000000002</value>
      </custom-attribute>
      <custom-attribute name="PID_OWNER_SellerID@Farma-MasterRepository" dt:dt="string">Farmaz</custom-attribute>
      <custom-attribute name="PID_VALUE_SellerID@Farma-MasterRepository" dt:dt="string">101234</custom-attribute>
      <custom-attribute name="Weight" dt:dt="quantity" xml:lang="it-IT">50.000 kg</custom-attribute>
    </custom-attributes>
  </product>
</enfinity>';
SELECT @xml

this upon select gave me error:
Msg 9459, Level 16, State 1, Line 2
XML parsing: line 25, character 52, undeclared prefix
OK I think is something related to the node <custom-attribute name="GTINs" dt:dt="string">

'cause if I remove it I got the select

DECLARE @xml as xml
SET @xml = N'
<enfinity  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xml="http://www.w3.org/XML/1998/namespace" major="6" minor="1" family="enfinity" branch="enterprise" build="7.4.5.1.46">
  <product sku="101234">
    <sku>101234</sku>
    <long-description xml:lang="en-US">Morazol</long-description>
    <long-description xml:lang="it-IT">Morazol</long-description>
    <name xml:lang="en-US">Zeolithe A1</name>
    <name xml:lang="it-IT">Zeolithe </name>
    <short-description xml:lang="en-US">Zeolithe A1</short-description>
    <short-description xml:lang="it-IT">Zeolithe A1</short-description>
    <product-type name="Basic" domain="system" />
    <template>product/GeneralProductInformationInclude</template>
    <sharing-groups>
      <sharing-group id="B2C_IT_GROUP" organization="Pharmaton" />
    </sharing-groups>
    <online>0</online>
    <manufacturer>
      <manufacturer-name>ZeoDiv</manufacturer-name>
      <manufacturer-sku>ZeoDiv</manufacturer-sku>
    </manufacturer>
    <category-links>
      <category-link name="CAT_LEV3_89" domain="ProdottiFarma" default="1" hotdeal="0" />
    </category-links>
  </product>
</enfinity>';
SELECT @xml

Anyway... over this first problem... my final goal is have a select or a update for a final result like this:

<?xml version="1.0" encoding="utf-8"?>
<products xmlns:dt="http://www.pharmatom.com/xml/ns/enfinity/6.5/core/impex-dt">
 <product sku="101234">
    <sku>101234</sku>
    <long-description xml:lang="en-US">Morazol</long-description>
    <long-description xml:lang="it-IT">Morazol</long-description>
    <name xml:lang="en-US">Zeolithe A1</name>
    <name xml:lang="it-IT">Zeolithe </name>
    <short-description xml:lang="en-US">Zeolithe A1</short-description>
    <short-description xml:lang="it-IT">Zeolithe A1</short-description>
    <product-type name="Basic" domain="system" />
    <template>product/GeneralProductInformationInclude</template>
    <sharing-groups>
      <sharing-group id="B2C_IT_GROUP" organization="Pharmaton" />
    </sharing-groups>
    <online>0</online>
    <manufacturer>
      <manufacturer-name>ZeoDiv</manufacturer-name>
      <manufacturer-sku>ZeoDiv</manufacturer-sku>
    </manufacturer>
    <category-links>
      <category-link name="CAT_LEV3_89" domain="ProdottiFarma" default="1" hotdeal="0" />
    </category-links>
    <custom-attributes>
      <custom-attribute name="GTINs" dt:dt="string">
        <value>0000000000001</value>
        <value>0000000000002</value>
      </custom-attribute>
      <custom-attribute name="PID_OWNER_SellerID@Farma-MasterRepository" dt:dt="string">Farmaz</custom-attribute>
      <custom-attribute name="PID_VALUE_SellerID@Farma-MasterRepository" dt:dt="string">101234</custom-attribute>
      <custom-attribute name="Weight" dt:dt="quantity" xml:lang="it-IT">50.000 kg</custom-attribute>
    </custom-attributes>
  </product>
</products>	

It's changed the root tag: <enfinity to <products.
Alen Italy

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 24,946 Reputation points
    2020-12-02T17:24:25.313+00:00

    The input XML is missing a namespace declaration. So I added an arbitrary Microsoft namespace:

    xmlns:dt="urn:schemas-microsoft-com:datatypes"
    

    I had to add it to the XML to make it legit.
    After that XQuery does exactly what is needed.

    SQL

    DECLARE @xml XML =
    N'<enfinity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              xmlns:xml="http://www.w3.org/XML/1998/namespace" 
       xmlns:dt="urn:schemas-microsoft-com:datatypes"
       major="6" minor="1"
              family="enfinity" branch="enterprise" build="7.4.5.1.46">
     <product sku="101234">
     <sku>101234</sku>
     <long-description xml:lang="en-US">Morazol</long-description>
     <long-description xml:lang="it-IT">Morazol</long-description>
     <name xml:lang="en-US">Zeolithe A1</name>
     <name xml:lang="it-IT">Zeolithe</name>
     <short-description xml:lang="en-US">Zeolithe A1</short-description>
     <short-description xml:lang="it-IT">Zeolithe A1</short-description>
     <product-type name="Basic" domain="system"/>
     <template>product/GeneralProductInformationInclude</template>
     <sharing-groups>
     <sharing-group id="B2C_IT_GROUP" organization="Pharmaton"/>
     </sharing-groups>
     <online>0</online>
     <manufacturer>
     <manufacturer-name>ZeoDiv</manufacturer-name>
     <manufacturer-sku>ZeoDiv</manufacturer-sku>
     </manufacturer>
     <category-links>
     <category-link name="CAT_LEV3_89" domain="ProdottiFarma" default="1"
                    hotdeal="0"/>
     </category-links>
     <custom-attributes>
     <custom-attribute name="GTINs" dt:dt="string">
     <value>0000000000001</value>
     <value>0000000000002</value>
     </custom-attribute>
     <custom-attribute name="PID_OWNER_SellerID@Farma-MasterRepository"
                       dt:dt="string">Farmaz</custom-attribute>
     <custom-attribute name="PID_VALUE_SellerID@Farma-MasterRepository"
                       dt:dt="string">101234</custom-attribute>
     <custom-attribute name="Weight" dt:dt="quantity" xml:lang="it-IT">50.000 kg</custom-attribute>
     </custom-attributes>
     </product>
    </enfinity>';
    
    -- before
    SELECT @xml;
    
    -- after
    SELECT @xml.query('declare namespace 
    dt="http://www.pharmatom.com/xml/ns/enfinity/6.5/core/impex-dt";
    <products xmlns:dt="http://www.pharmatom.com/xml/ns/enfinity/6.5/core/impex-dt">
     {/enfinity/product}
    </products>');
    

0 additional answers

Sort by: Most helpful