xml vs substring
/SQL/xml vs substring.sql
SELECT
boot_id
--, CAST(boot_xml_tmp AS XML).value('(/reservations/reservation/customer/last_name)[1]', 'VARCHAR(50)') AS bti_nom
, SUBSTRING(
boot_xml_tmp
, CHARINDEX('<last_name>', boot_xml_tmp) + LEN('<last_name>')
,
CASE WHEN (CHARINDEX('</last_name>', boot_xml_tmp)>0)
THEN
CHARINDEX('</last_name>', boot_xml_tmp)
- CHARINDEX('<last_name>', boot_xml_tmp)
- LEN('<last_name>')
ELSE
0
END
)
FROM booking_trace WITH(NOLOCK)
WHERE 1=1
--and boot_id = 72470
and boot_date > DATEADD(DAY, -365, GETDATE())
AND boot_id NOT IN (
SELECT boot_id FROM booking_trace_index
)
-- 72470
-- 72561
-- 74297