Share via

Replace root element from XML database field

Alen Cappelletti 992 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,601 questions
0 comments
Accepted answer
  1. Yitzhak Khabinsky 25,731 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
Most helpful Newest Oldest