1
votes

Les méthodes XML ne sont pas autorisées dans une clause GROUP BY

J'essaie d'ajouter un Group By à ma requête XML simple. Je vois une question similaire dans le lien ci-dessous, mais je n'ai pas pu la rattraper avec la mienne.

Lien

Mon exemple de code

DECLARE @xml VARCHAR(8000)  = '<root><que trp=''100001'' ccid=''59748''  /></root>'
DECLARE @recordXml XML = @xml

SELECT
            T.a.value('@trp[1]','CHAR(6)') AS trip_no,
            MAX(T.a.value('@ccid[1]','INT')) AS check_call_id
        FROM @recordXml.nodes('/root/que')T(a)
        WHERE
            LEN(T.a.value('@trp[1]','CHAR(6)')) = 6
            AND ISNUMERIC(T.a.value('@trp[1]','CHAR(6)')) = 1
            AND CONVERT(INT, T.a.value('@trp[1]','CHAR(6)')) > 0
        GROUP BY T.a.value('@trp[1]','CHAR(6)')

Toute aide est appréciée pour résoudre ce problème.

sql sql-server

0 commentaires

3 Réponses :

3
votes

Utiliser une sous-requête ...

SELECT
    trip_no,
    MAX(check_call_id)   AS check_call_id
FROM
(
    SELECT
        T.a.value('@trp[1]' , 'CHAR(6)') AS trip_no,
        T.a.value('@ccid[1]', 'INT'    ) AS check_call_id
    FROM
        @recordXml.nodes('/root/que')T(a)
    WHERE
        LEN(T.a.value('@trp[1]','CHAR(6)')) = 6
        AND ISNUMERIC(T.a.value('@trp[1]','CHAR(6)')) = 1
        AND CONVERT(INT, T.a.value('@trp[1]','CHAR(6)')) > 0
)
    AS parsed_xml
GROUP BY
    trip_no

1 commentaires

Merci! C'était la meilleure façon de résoudre le problème. J'apprécie votre temps!

1
votes

Vous devrez encapsuler la requête non agrégée dans un CTE ou une sous-requête, puis effectuer l'agrégation à ce sujet. Par exemple:

WITH CTE AS
    (SELECT T.a.value('@trp[1]', 'CHAR(6)') AS trip_no,
            T.a.value('@ccid[1]', 'INT') AS check_call_id
     FROM @recordXml.nodes('/root/que') T(a)
     WHERE LEN(T.a.value('@trp[1]', 'CHAR(6)')) = 6
       AND TRY_CONVERT(int,T.a.value('@trp[1]', 'CHAR(6)')) > 0) --If this has decimals, use decimal instead of int
SELECT trip_no,
       MAX(check_call_id) AS check_call_id
FROM CTE
GROUP BY trip_no;

Sur une note différente, je déconseille ISNUMERIC et suggère d'utiliser TRY_CONVERT . ISNUMERIC peut avoir un comportement étrange comme renvoyer 1 pour ISNUMERIC ('.') mais une conversion vers n'importe quel type de données numérique pour '. ' échouerait. De plus, si ISNUMERIC échoue, la clause suivante CONVERT (int, Tavalue ('@ trp [1]', 'CHAR (6)'))> 0 sera échec et erreur. Vous pouvez donc être plus concis et faire:

WITH CTE AS
    (SELECT T.a.value('@trp[1]', 'CHAR(6)') AS trip_no,
            T.a.value('@ccid[1]', 'INT') AS check_call_id
     FROM @recordXml.nodes('/root/que') T(a)
     WHERE LEN(T.a.value('@trp[1]', 'CHAR(6)')) = 6
       AND ISNUMERIC(T.a.value('@trp[1]', 'CHAR(6)')) = 1
       AND CONVERT(int, T.a.value('@trp[1]', 'CHAR(6)')) > 0)
SELECT trip_no,
       MAX(check_call_id)
FROM CTE
GROUP BY trip_no;

2 commentaires

Merci! Cela a été utile. J'ai d'autres jointures, donc j'ai préféré l'approche de sous-requête de MatBailie. Lorsque j'essaie de mettre en œuvre certains de vos bons points, comme utiliser TRY_CONVERT au lieu des deux AND dans ma requête, j'obtiens une erreur «La fonction TRY_CONVERT» nécessitait 3 arguments. Cela a-t-il fonctionné pour vous comme dans votre code?

Oui, cela a fonctionné. TRY_CONVERT n'a besoin que de 2 paramètres, bien qu'un troisième puisse être fourni comme code de style, mais ce n'est que lorsque vous travaillez avec des types de données de date et d'heure et le type de données xml ; ce qui n'est pas le cas ici et c'est facultatif de toute façon. Est-ce vraiment l'erreur que vous avez reçue?

0
votes

DECLARE @xml VARCHAR(8000)  = '<root><que trp=''100001'' ccid=''59748''  /></root>'
DECLARE @recordXml XML = @xml

SELECT
    dat.trip_no
    , MAX( dat.check_call_id ) AS max_check_call_id
FROM (

    SELECT
        T.a.value( '@trp[1]','CHAR(6)' ) AS trip_no,
        T.a.value( '@ccid[1]','INT' ) AS check_call_id
    FROM @recordXml.nodes( '/root/que' ) T( a )
    WHERE
        LEN( T.a.value('@trp[1]','CHAR(6)') ) = 6
        AND ISNUMERIC( T.a.value( '@trp[1]','CHAR(6)' ) ) = 1
        AND CONVERT( INT, T.a.value( '@trp[1]','CHAR(6)' ) ) > 0

) AS dat
GROUP BY
    dat.trip_no

1 commentaires

Merci pour le temps ... cela a été utile!