SELECT dans un champ XML avec noeud multiple

Version imprimable

Bonjour à tous,

Je cherche à faire un SELECT sur un champ XML de ma table mais je bloque sur un point.
SGBD: SQL Server

Mon XML:

Code:

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
<SectionAnnotations xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <SectionNumber>0</SectionNumber>
  <Pages>
    <PageAnnotations>
      <PageNumber>2</PageNumber>
      <Annotations>
        <Layer xmlns="http://test" Id="1234">
          <TextStamp Transparent="true" Id="1" UserName="dwadmin">
            <Created User="dwadmin" Time="2022-09-23T15:10:02Z" />
            <Location Left="7085" Top="831" Width="1980" Height="886" />
            <HeadFont FontName="Arial" FontSize="220" />
            <Font FontName="Arial" FontSize="220" />
            <Value>X</Value>
          </TextStamp>
        </Layer>
      </Annotations>
    </PageAnnotations>
	  <PageAnnotations>
		  <PageNumber>4</PageNumber>
		  <Annotations>
			  <Layer xmlns="http://test" Id="1234">
				  <TextStamp Transparent="true" Id="1" UserName="dwadmin">
					  <Created User="dwadmin" Time="2022-09-23T15:10:02Z" />
					  <Location Left="7085" Top="831" Width="1980" Height="886" />
					  <HeadFont FontName="Arial" FontSize="220" />
					  <Font FontName="Arial" FontSize="220" />
					  <Value>X</Value>
				  </TextStamp>
			  </Layer>
		  </Annotations>
	  </PageAnnotations>
    <PageAnnotations>
      <PageNumber>8</PageNumber>
      <Annotations>
        <Layer xmlns="http://test" Id="1234">
          <TextStamp Transparent="true" Id="2" ShowUser="false" ShowDate="false" ShowTime="false">
            <Created User="dwadmin" Time="2022-09-23T15:11:23Z" />
            <Location Left="4498" Top="1488" Width="5216" Height="353" />
            <HeadFont FontName="Arial" FontSize="220" />
            <Font FontName="Arial" FontSize="220" />
            <Value>Y</Value>
          </TextStamp>
        </Layer>
      </Annotations>
    </PageAnnotations>
  </Pages>
</SectionAnnotations>

Le noeud <PageAnnotations> peut se répéter X fois.
Je dois récupérer les valeurs de PageNumber si l'ID dans TextStamp est égal à 1.
Dans le cas où j'ai plusieurs noeud avecdes ID TextStamp à 1 je dois récupérer toutes les valeurs de PageNumber en les concaténant (séparation avec une virgule).

J'arrive facilement à récupérer la 1ere valeur de PageNumber avec:

Code:

1
2
SELECT MONCHAMPXML.value('(/SectionAnnotations//PageNumber/node())[1]', 'nvarchar(max)') as PageNumber
  FROM  MaTable

Mais je vois pas comment boucler sur un champ XML ni comment faire la concaténation si plusieurs valeurs.

Des pistes à me proposer?

Merci pour votre aide,

Il faut d'abord tabulariser le XML avec la méthode nodes appliquée à votre XML, puis extraire avec la méthode query.

Exemple:

Code:

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
DECLARE @XML XML =
N'<SectionAnnotations xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <SectionNumber>0</SectionNumber>
  <Pages>
    <PageAnnotations>
      <PageNumber>2</PageNumber>
      <Annotations>
        <Layer xmlns="http://test" Id="1234">
          <TextStamp Transparent="true" Id="1" UserName="dwadmin">
            <Created User="dwadmin" Time="2022-09-23T15:10:02Z" />
            <Location Left="7085" Top="831" Width="1980" Height="886" />
            <HeadFont FontName="Arial" FontSize="220" />
            <Font FontName="Arial" FontSize="220" />
            <Value>X</Value>
          </TextStamp>
        </Layer>
      </Annotations>
    </PageAnnotations>
	  <PageAnnotations>
		  <PageNumber>4</PageNumber>
		  <Annotations>
			  <Layer xmlns="http://test" Id="1234">
				  <TextStamp Transparent="true" Id="1" UserName="dwadmin">
					  <Created User="dwadmin" Time="2022-09-23T15:10:02Z" />
					  <Location Left="7085" Top="831" Width="1980" Height="886" />
					  <HeadFont FontName="Arial" FontSize="220" />
					  <Font FontName="Arial" FontSize="220" />
					  <Value>X</Value>
				  </TextStamp>
			  </Layer>
		  </Annotations>
	  </PageAnnotations>
    <PageAnnotations>
      <PageNumber>8</PageNumber>
      <Annotations>
        <Layer xmlns="http://test" Id="1234">
          <TextStamp Transparent="true" Id="2" ShowUser="false" ShowDate="false" ShowTime="false">
            <Created User="dwadmin" Time="2022-09-23T15:11:23Z" />
            <Location Left="4498" Top="1488" Width="5216" Height="353" />
            <HeadFont FontName="Arial" FontSize="220" />
            <Font FontName="Arial" FontSize="220" />
            <Value>Y</Value>
          </TextStamp>
        </Layer>
      </Annotations>
    </PageAnnotations>
  </Pages>
</SectionAnnotations>'
 
SELECT X.query('.'), 
       ROW_NUMBER() OVER(ORDER BY CAST(X.query('.') AS VARCHAR(256))) AS OCCURRENCE, 
       X.value('(.)[1]', 'int') AS PAGE_NUMBER
FROM   @XML.nodes('/SectionAnnotations//PageNumber') as T(X)

C'est évidemment la requête finale qui nous intéresse :

Code:

1
2
3
4
SELECT X.query('.'), 
       ROW_NUMBER() OVER(ORDER BY CAST(X.query('.') AS VARCHAR(256))) AS OCCURRENCE, 
       X.value('(.)[1]', 'int') AS PAGE_NUMBER
FROM   @XML.nodes('/SectionAnnotations//PageNumber') as T(X)

Qui donne :

Code:

1
2
3
4
5
XML_NODE                      OCCURRENCE  PAGE_NUMBER
----------------------------- ----------- -----------
<PageNumber>2</PageNumber>              1           2
<PageNumber>4</PageNumber>              2           4
<PageNumber>8</PageNumber>              3           8

A +

Au top merci :plusser:
Et cela m'amène sur un autre problème...
Mon XML est dans un champ de ma table. La table contient 2 champs (ID, MonChampXML).
Je souhaite créer une vue qui me remonte tous les PAGE_NUMBER pour chaque ID de la table.

Dans mon exemple cela donnerait ça:
Code:

1 2 3 4 5 6 7 8 9 ID XML_NODE OCCURRENCE PAGE_NUMBER ----- ------------------------------ ----------- ----------------------- 1 <PageNumber>2</PageNumber> 1 2 1 <PageNumber>4</PageNumber> 2 4 1 <PageNumber>8</PageNumber> 3 8 2 <PageNumber>2</PageNumber> 1 2 2 <PageNumber>4</PageNumber> 2 4 etc...
Si j'exécute la requête sur un ID donné ça fonctionne parfaitement mais quand je souhaite le faire sur l'ensemble de la table j'ai une erreur. Ce qui me semble logique mais je vois pas comment faire la requête:
Code:

1 2 3 4 5 6 7 8 9 10 declare @XML xml declare @ID int set @XML = (SELECT ANNOTATIONS FROM [dwdata].[dbo].[RH_SEC] where docid=870) set @ID = (SELECT DOCID FROM [dwdata].[dbo].[RH_SEC] where docid=870) SELECT @ID as ID,X.value('(.)[1]', 'int') AS PAGE_NUMBER FROM @XML.nodes('/SectionAnnotations//PageNumber') as T(X)
Si j'enlève where docid=870, j'ai cette erreur:

Code:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Voici :

Avec ces données :

Code:

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
CREATE TABLE #T (id int IDENTITY, xdata XML)
GO
DECLARE @XML XML =
N'<SectionAnnotations xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <SectionNumber>0</SectionNumber>
  <Pages>
    <PageAnnotations>
      <PageNumber>2</PageNumber>
      <Annotations>
        <Layer xmlns="http://test" Id="1234">
          <TextStamp Transparent="true" Id="1" UserName="dwadmin">
            <Created User="dwadmin" Time="2022-09-23T15:10:02Z" />
            <Location Left="7085" Top="831" Width="1980" Height="886" />
            <HeadFont FontName="Arial" FontSize="220" />
            <Font FontName="Arial" FontSize="220" />
            <Value>X</Value>
          </TextStamp>
        </Layer>
      </Annotations>
    </PageAnnotations>
	  <PageAnnotations>
		  <PageNumber>4</PageNumber>
		  <Annotations>
			  <Layer xmlns="http://test" Id="1234">
				  <TextStamp Transparent="true" Id="1" UserName="dwadmin">
					  <Created User="dwadmin" Time="2022-09-23T15:10:02Z" />
					  <Location Left="7085" Top="831" Width="1980" Height="886" />
					  <HeadFont FontName="Arial" FontSize="220" />
					  <Font FontName="Arial" FontSize="220" />
					  <Value>X</Value>
				  </TextStamp>
			  </Layer>
		  </Annotations>
	  </PageAnnotations>
    <PageAnnotations>
      <PageNumber>8</PageNumber>
      <Annotations>
        <Layer xmlns="http://test" Id="1234">
          <TextStamp Transparent="true" Id="2" ShowUser="false" ShowDate="false" ShowTime="false">
            <Created User="dwadmin" Time="2022-09-23T15:11:23Z" />
            <Location Left="4498" Top="1488" Width="5216" Height="353" />
            <HeadFont FontName="Arial" FontSize="220" />
            <Font FontName="Arial" FontSize="220" />
            <Value>Y</Value>
          </TextStamp>
        </Layer>
      </Annotations>
    </PageAnnotations>
  </Pages>
</SectionAnnotations>'
 
INSERT INTO #T VALUES
(@XML)
GO 5

La requête finale :

Code:

1
2
3
4
5
6
7
8
WITH T AS
(
SELECT id, X.query('.') X_PAGES
FROM  #T   
      CROSS APPLY xdata.nodes('/SectionAnnotations//PageNumber') as T(X)
)
SELECT *, X_PAGES.value('(PageNumber)[1]', 'int') AS PAGE
FROM   T;

Qui donne :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
id          X_PAGES                         PAGE
----------- ------------------------------- -----------
1           <PageNumber>2</PageNumber>      2
1           <PageNumber>4</PageNumber>      4
1           <PageNumber>8</PageNumber>      8
2           <PageNumber>2</PageNumber>      2
2           <PageNumber>4</PageNumber>      4
2           <PageNumber>8</PageNumber>      8
3           <PageNumber>2</PageNumber>      2
3           <PageNumber>4</PageNumber>      4
3           <PageNumber>8</PageNumber>      8
4           <PageNumber>2</PageNumber>      2
4           <PageNumber>4</PageNumber>      4
4           <PageNumber>8</PageNumber>      8
5           <PageNumber>2</PageNumber>      2
5           <PageNumber>4</PageNumber>      4
5           <PageNumber>8</PageNumber>      8

A +

03/10/2022, 12h09
ultrakas80

Un grand merci SQLPro c'est exactement ce que je voulais !