¿Se podrá desde SQLServer <2016 parsear a formato JSON?
Publicado por Yersson (2 intervenciones) el 08/07/2022 23:27:06
Buen día y espero estén bien.
Mi pregunta viene a que desde el SQLServer 2016 existe la función FOR JSON la cual convierte a tu Query en un formato JSON, pero quisiera saber si conocen de una función creada de forma externa o que podría tener yo de guía para crear una función similar.
Gracias por leer y dejo acá un ejemplo de como es ahora en SQL Server y el resultado que sale.
muchas gracias por su atención y lectura.
Mi pregunta viene a que desde el SQLServer 2016 existe la función FOR JSON la cual convierte a tu Query en un formato JSON, pero quisiera saber si conocen de una función creada de forma externa o que podría tener yo de guía para crear una función similar.
Gracias por leer y dejo acá un ejemplo de como es ahora en SQL Server y el resultado que sale.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
SELECT
FORMAT(FAC.fecha, 'dd/MM/yyyy') AS DocDate,
FORMAT(FAC.fechavence, 'dd/MM/yyyy') AS DocDueDate,
RTRIM(CASEWHEN SOC.esempleado = '1' THEN 'C' + FAC.codigo
ELSE FAC.codigoEND) AS CardCode,
REPLACE(FAC.referencia, FAC.referencia, 'null') AS NumAtCard,
REPLACE(FAC.totalgeneral, '.', ',') AS DocTotal,
SUBSTRING(FAC.observacion, 0, 250) AS Comments,
@Series AS Series,FORMAT(FAC.fecha, 'dd/MM/yyyy') AS TaxDate,
CASE
WHEN FAC.codbodega = 'PV-PER' THEN 'PV-DQU'
ELSE FAC.codbodega END AS Filler,RTRIM(FAC.consecutivo) AS U_NumPorcipos,
RTRIM(CASEWHEN SOC.esempleado = 1 THEN 'C' + FAC.codigo
ELSE FAC.codigoEND) AS U_HBT_TERCERO,
RTRIM(FAC.consecutivo) AS DocNum,
FAC.codtipodcto AS CodTipoDcto,
CASE
WHEN FAC.codbodega = 'PV-PER' THEN 'PV-DQU'
ELSE FAC.codbodega END AS CodBodega,'$' AS DocCur,
'ZZZ' AS U_HBT_MedPag,
'01-A' AS U_HBT_DocNoTributar,
(SELECT CASEWHEN MFAC.codigo IN ('ME-14009',
'ME-14010',
'ME-14011') THEN 'IMP-BOLSAP'
ELSE MFAC.codigo END AS ItemCode,REPLACE(MFAC.cantidad, '.', ',') AS Quantity,
REPLACE(MFAC.descuento, '.', ',') AS DiscPrcnt,
CASE
WHEN MFAC.codbodega = 'PV-PER' THEN 'PV-DQU'
ELSE MFAC.codbodega END AS WhsCode, @OcrCode3 AS OcrCode3, @OcrCode3 AS CogsOcrCo3,RTRIM(MFAC.consecutivo) AS DocEntry,
CASE
WHEN MFAC.codbodega = 'PV-PER' THEN 'PV-DQU'
ELSE MFAC.codbodega END AS codbodega,MFAC.codtipodcto,
ART.manejalote,
MFAC.idmvto,
REPLACE(MFAC.valor, '.', ',') AS PriceBefDi,
'ZZZ' AS U_HBT_MedPag,
'01-A' AS U_HBT_DocNoTributarFROM tblmvtofacturas AS MFAC
JOIN tblencfacturas AS ENC ON ENC.Consecutivo = MFAC.consecutivo
AND ENC.CodTipoDcto = MFAC.codtipodcto
AND ENC.CodBodega = CASE
WHEN MFAC.codbodega = 'PV-PER' THEN 'PV-DQU'
ELSE MFAC.codbodegaEND
JOIN tblarticulos AS ART ON ART.codigo = MFAC.codigo
WHERE ART.CODIGO NOT IN ('MOD-00001')
AND MFAC.consecutivo = @consecutivo
FOR JSON PATH) AS DocumentLinesFROM tblencfacturas AS FAC
JOIN tblsocio_negocios AS SOC ON SOC.codigo = FAC.codigo
WHERE FAC.codtipodcto = 'FA'
AND FAC.consecutivo = @consecutivo
FOR JSON PATH,WITHOUT_ARRAY_WRAPPER
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
{"DocDate": "08/06/2012",
"DocDueDate": "16/06/2012",
"CardCode": "C-1128437036",
"NumAtCard": "null",
"DocTotal": "131399,98",
"Comments": "",
"Series": "1759",
"TaxDate": "08/06/2012",
"Filler": "PV-ENV",
"U_NumPorcipos": "1260000004",
"U_HBT_TERCERO": "C-1128437036",
"DocNum": "1260000004",
"CodTipoDcto": "FA",
"CodBodega": "PV-ENV",
"DocCur": "$",
"U_HBT_MedPag": "ZZZ",
"U_HBT_DocNoTributar": "01-A",
"DocumentLines": [
{"ItemCode": "PTD-7002",
"Quantity": "4,000000",
"DiscPrcnt": "0,00",
"WhsCode": "PV-ENV",
"OcrCode3": "3611",
"CogsOcrCo3": "3611",
"DocEntry": "1260000004",
"codbodega": "PV-ENV",
"codtipodcto": "FA",
"manejalote": true,
"idmvto": 61762,
"PriceBefDi": "5727,27",
"U_HBT_MedPag": "ZZZ",
"U_HBT_DocNoTributar": "01-A"
},
{"ItemCode": "PTD-7005",
"Quantity": "3,000000",
"DiscPrcnt": "0,00",
"WhsCode": "PV-ENV",
"OcrCode3": "3611",
"CogsOcrCo3": "3611",
"DocEntry": "1260000004",
"codbodega": "PV-ENV",
"codtipodcto": "FA",
"manejalote": true,
"idmvto": 61764,
"PriceBefDi": "6136,36",
"U_HBT_MedPag": "ZZZ",
"U_HBT_DocNoTributar": "01-A"
},
{"ItemCode": "PTD-7017",
"Quantity": "1,000000",
"DiscPrcnt": "0,00",
"WhsCode": "PV-ENV",
"OcrCode3": "3611",
"CogsOcrCo3": "3611",
"DocEntry": "1260000004",
"codbodega": "PV-ENV",
"codtipodcto": "FA",
"manejalote": true,
"idmvto": 61763,
"PriceBefDi": "9981,82",
"U_HBT_MedPag": "ZZZ",
"U_HBT_DocNoTributar": "01-A"
},
{"ItemCode": "PTD-7023",
"Quantity": "5,000000",
"DiscPrcnt": "0,00",
"WhsCode": "PV-ENV",
"OcrCode3": "3611",
"CogsOcrCo3": "3611",
"DocEntry": "1260000004",
"codbodega": "PV-ENV",
"codtipodcto": "FA",
"manejalote": true,
"idmvto": 61761,
"PriceBefDi": "3681,82",
"U_HBT_MedPag": "ZZZ",
"U_HBT_DocNoTributar": "01-A"
},
{"ItemCode": "PTD-7028",
"Quantity": "1,000000",
"DiscPrcnt": "0,00",
"WhsCode": "PV-ENV",
"OcrCode3": "3611",
"CogsOcrCo3": "3611",
"DocEntry": "1260000004",
"codbodega": "PV-ENV",
"codtipodcto": "FA",
"manejalote": true,
"idmvto": 61767,
"PriceBefDi": "3518,18",
"U_HBT_MedPag": "ZZZ",
"U_HBT_DocNoTributar": "01-A"
},
{"ItemCode": "PTD-7032",
"Quantity": "4,000000",
"DiscPrcnt": "0,00",
"WhsCode": "PV-ENV",
"OcrCode3": "3611",
"CogsOcrCo3": "3611",
"DocEntry": "1260000004",
"codbodega": "PV-ENV",
"codtipodcto": "FA",
"manejalote": true,
"idmvto": 61760,
"PriceBefDi": "3845,45",
"U_HBT_MedPag": "ZZZ",
"U_HBT_DocNoTributar": "01-A"
},
{"ItemCode": "PTD-7036",
"Quantity": "1,000000",
"DiscPrcnt": "0,00",
"WhsCode": "PV-ENV",
"OcrCode3": "3611",
"CogsOcrCo3": "3611",
"DocEntry": "1260000004",
"codbodega": "PV-ENV",
"codtipodcto": "FA",
"manejalote": true,
"idmvto": 61765,
"PriceBefDi": "20290,91",
"U_HBT_MedPag": "ZZZ",
"U_HBT_DocNoTributar": "01-A"
},
{"ItemCode": "PTD-7037",
"Quantity": "1,000000",
"DiscPrcnt": "0,00",
"WhsCode": "PV-ENV",
"OcrCode3": "3611",
"CogsOcrCo3": "3611",
"DocEntry": "1260000004",
"codbodega": "PV-ENV",
"codtipodcto": "FA",
"manejalote": true,
"idmvto": 61766,
"PriceBefDi": "10554,55",
"U_HBT_MedPag": "ZZZ",
"U_HBT_DocNoTributar": "01-A"
} ]}muchas gracias por su atención y lectura.
Valora esta pregunta
0