Bdtjtk

I don't understand why extractvalue doesn't returns values of attributes when use MySQL concat function.

SET @xml = '<items>

<docHeader companyIdIssuer="company" docTypeId="2" companyIdReceiver="nin" dateIssue="2018-12-28">

    <attribute attributeId="2" value="1236"/>

    <docDetail productId="1" valor="some value">

        <variable variableId="6" value="12.3" unitId="34"/>

        <variable variableId="7" value="2.3" unitId="34"/>

        <variable variableId="3" value="4.325" unitId="34"/>

        <variable variableId="5" value="20" unitId="106"/>

        <attribute attributeId="1" value="1236"/>

    </docDetail>

</docHeader>

</items>';



SET @headerAttributePath = '//items/docHeader[1]/attribute[1]';

SET @detailPath = '//items/docHeader[1]/docDetail[1]';

SET @detailAttributePath = '//items/docHeader[1]/docDetail[1]/attribute[1]';



select  extractValue(@xml, concat(@headerAttributePath, '/@attributeId')) AS attIdConcat,

        extractValue(@xml, '//items/docHeader[1]/attribute[1]/@attributeId') AS attIdPlain,

        extractValue(@xml, concat(@headerAttributePath, '/@value')) AS attValueConcat,

        extractValue(@xml, '//items/docHeader[1]/attribute[1]/@value') AS attValuePlain,

        @headerAttributePath AS attHeaderPath,

        concat(@headerAttributePath, '/@value') AS attValuePathConcat,

        '//items/docHeader[1]/attribute[1]/@value' AS attValuePathPlain,

        STRCMP(concat(@headerAttributePath, '/@value'), '//items/docHeader[1]/attribute[1]/@value') AS pathStrComp,

        extractValue(@xml, concat(@detailPath, '/@valor')) AS detValueConcat,

        extractValue(@xml, concat(@detailAttributePath, '/@attributeId')) AS attDetIdConcat,

        extractValue(@xml, concat(@detailAttributePath, '/@value')) AS attDetValueConcat;

Sample: SQL Fiddle

A rather peculiar case, it seems that the combination of some paths, for example, items -> docHeader -> attribute -> @value or items -> docHeader -> docDetail -> @valor, produces errors in the parsing, which is a bug.

Some workarounds:

• If you can not change the name of any of the elements, you can use the CAST() function in CONCAT function.


• Rename one of the elements, for example, docHeader to doc_Header, attribute to attr or valor to _valor.

See db-fiddle.

As mentioned @Barmar, in MySQL 8.0, everything works as expected, see db-fiddle.

UPDATE

After some additional testing, we can see the problem also occurs when the total length of the path does not achieve a certain character length, see db-fiddle.