SELECT dans un champ XML avec noeud multiple

**ultrakas80** · 24/09/2022, 18h16

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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,

**SQLpro** · 24/09/2022, 18h47

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 +

**ultrakas80** · 25/09/2022, 12h13

Au top merci

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**SQLpro** · 28/09/2022, 14h01

Voici :

Avec ces données :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 +

**ultrakas80** · 03/10/2022, 12h09

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

SELECT dans un champ XML avec noeud multiple

Développement SQL Server

Discussions similaires

Partager

Partager