'How to multiply scalar value from OPENJSON array ? (from JSON to XML)

I'm mapping JSON to XML. I haven't found a similar question or example in the docs for this. I'm not able to create an additional subquery as I'm already nesting inside a larger query. Is it possible to update my scalar values (DECIMAL) through multiplication?

How do I divide the value ofLENGTH, WIDTH and HEIGHT by 100 ?

My current query :

(SELECT * 
         FROM OPENJSON(JSON_QUERY(@json, '$.Blad1'))
         WITH (
                [LENGTH] DECIMAL N'$."Lengte (cm)"',
                [WIDTH] DECIMAL N'$."breedte (cm)"', 
                [HEIGHT] DECIMAL N'$."Hoogte (cm)"'
                )
        FOR XML PATH(N'UNIT'), ROOT(N'UNITS'), TYPE
)

This results in the following output :

<ITEM>
    <UNITS>
      <UNIT>
        <LENGTH>38</LENGTH>
        <WIDTH>38</WIDTH>
        <HEIGHT>26</HEIGHT>
      </UNIT>
    </UNITS>
</ITEM>

These values are in cm right now, i need them in M instead. Using this WITH clause, how do i go to :

<ITEM>
    <UNITS>
      <UNIT>
        <LENGTH>0.38</LENGTH>
        <WIDTH>0.38</WIDTH>
        <HEIGHT>0.26</HEIGHT>
      </UNIT>
    </UNITS>
</ITEM>

JSON snippet :

{
"Blad1": 
  [
    {
      "Artikelcode": "0111000000",
      "Barcode": "5414365211134",
      "Omschrijving": "Nylon Leiband",
      "Eenheid": "karton",
      "# Individuele eenheden": 240.0,
      "Lengte (cm)": 38.0,
      "breedte (cm)": 38.0,
      "Hoogte (cm)": 26.0,
      "Bruto (KG)": 9.04,
      "Netto (KG)": 8.64,
      "Interne barcode": "5414365211141",
      "Barcode leverancier": null,
      "Eenheid_1": "pallet",
      "# Individuele eenheden_1": 8640.0,
      "Dozen op pallet": 36.0,
      "Lengte (cm)_1": 114.0,
      "breedte (cm)_1": 76.0,
      "Hoogte (cm)_1": 156.0,
      "Aantal verp. per laag": 6.0,
      "Max. aantal lagen": 6.0,
      "Bruto (KG)_1": 325.44,
      "Netto (KG)_1": 311.04,
      "Soort pallet": "EUR-Pallet"
    }
  ]
}



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source