'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 |
|---|
