XQuery [value ()]: valeur()' requiert un singleton (ou séquence vide), trouvé opérande de type 'xdt:untypedAtomic *'

j'essaie d'insérer des lignes dans une table en utilisant un select de XML. Je pense que je suis proche. Où vais-je tort?

declare @xmldata xml;
set @xmldata = '<Database>
                  <PurchaseDetails>
                    <PurchaseDetail>
                      <Upc>72594206916</Upc>
                      <Quantity>77</Quantity>
                      <PurchaseDate>9/2010</PurchaseDate>
                      <PurchaseCity>Dallas</PurchaseCity>
                      <PurchaseState>TX</PurchaseState>
                    </PurchaseDetail>
                    <PurchaseDetail>
                      <Upc>72594221854</Upc>
                      <Quantity>33</Quantity>
                      <PurchaseDate>12/2013</PurchaseDate>
                      <PurchaseCity>Nashville</PurchaseCity>
                      <PurchaseState>TN</PurchaseState>
                    </PurchaseDetail>
                  </PurchaseDetails>
                </Database>'

insert into PurchaseDetails
(Upc, Quantity, PurchaseDate, PurchaseCity, PurchaseState)
select
    x.Rec.value('Upc','char(11)'),
    x.Rec.value('Quantity','int'),
    x.Rec.value('PurchaseDate','varchar(7)'),
    x.Rec.value('PurchaseCity','varchar(50)'),
    x.Rec.value('PurchaseState','char(2)')
from @xmlData.nodes('//Database/PurchaseDetails/PurchaseDetail') as x(Rec)
32
demandé sur birdus 2013-11-13 01:46:53

5 réponses

un collègue s'était déjà attaqué à un problème semblable auparavant. Voici ce que nous est venu avec. Pas intuitif!

insert into PurchaseDetails
(Upc, Quantity, PurchaseDate, PurchaseCity, PurchaseState)
select
    pd.value('Upc[1]','char(11)'),
    pd.value('Quantity[1]','int'),
    pd.value('PurchaseDate[1]','varchar(7)'),
    pd.value('PurchaseCity[1]','varchar(50)'),
    pd.value('PurchaseState[1]','char(2)')
from @xmlData.nodes('//Database/PurchaseDetails') as x(Rec)
cross apply @xmlData.nodes('//Database/PurchaseDetails/PurchaseDetail') as i(pd)
41
répondu birdus 2013-11-15 19:08:57 

insert into PurchaseDetails(Upc, Quantity, PurchaseDate, PurchaseCity, PurchaseState)
select T.X.value('(Upc/text())[1]', 'char(11)'),
       T.X.value('(Quantity/text())[1]', 'int'),
       T.X.value('(PurchaseDate/text())[1]', 'varchar(7)'),
       T.X.value('(PurchaseCity/text())[1]', 'varchar(50)'),
       T.X.value('(PurchaseState/text())[1]', 'char(2)')
from @xmlData.nodes('/Database/PurchaseDetails/PurchaseDetail') as T(X)
17
répondu Mikael Eriksson 2014-03-31 14:04:23

Essayez ceci!

query() puis de la valeur()

exécutez ceci dans le serveur SQL et 100% travaillé

mettre un point (.) d'abord, puis la balise enfant.

La balise PurchaseDetail existe 2 fois donc le point (.) remplace la première et de la deuxième étiquette.

Le point peut empêcher l'utilisation de [1] sur XQuery.

Le point représente la première et la deuxième étiquette D'achat.

INSERT INTO PurchaseDetails(Upc, Quantity, PurchaseDate, PurchaseCity, PurchaseState)
SELECT col.query('./Upc').value('.', 'char(11)'),
    col.query('./Quantity').value('.', 'int'),
    col.query('./PurchaseDate').value('.', 'varchar(7)'),
    col.query('./PurchaseCity').value('.', 'varchar(50)'),
    col.query('./PurchaseState').value('.', 'char(2)')
FROM @xmlData.nodes('/Database/PurchaseDetails/PurchaseDetail') as ref(col)

c'est une requête plus simplifiée jusqu'à présent.

Voir si il fonctionne

15
répondu marion-jeff 2014-03-29 12:36:55 
select
    x.Rec.query('./Upc').value('.','char(11)')
    ,x.Rec.query('./Quantity').value('.','int')
    ,x.Rec.query('./PurchaseDate').value('.','varchar(7)')
    ,x.Rec.query('./PurchaseCity').value('.','varchar(50)')
    ,x.Rec.query('./PurchaseState').value('.','char(2)')
from @xmlData.nodes('/Database/PurchaseDetails/PurchaseDetail') as x(Rec)
13
répondu user809316 2013-11-29 20:18:25

se débattant avec un problème similaire, et a constaté que la réponse de @birdus ne fonctionnait pas si vous aviez des couches supplémentaires d'imbrication dans votre xml que vous référenciez dans votre XQuery, par exemple en supposant une forme XML légèrement différente, si vous aviez

T.x.value('PurchasePlace/PurchaseCity[1]','varchar(50)')

vous obtiendriez toujours l'erreur de singleton. Bien que la solution de @birdus fonctionne pour ce cas spécifique, une solution plus généralement applicable qui combine le meilleur de la solution de @birdus & @Mikael-Eriksson est de faire:

insert into PurchaseDetails(Upc, Quantity, PurchaseDate, PurchaseCity, PurchaseState)
select T.X.value('(Upc)[1]', 'char(11)'),
T.X.value('(Quantity)[1]', 'int'),
T.X.value('(PurchaseDate)[1]', 'varchar(7)'),
T.X.value('(PurchaseCity)[1]', 'varchar(50)'),
T.X.value('(PurchaseState)[1]', 'char(2)')
from @xmlData.nodes('/Database/PurchaseDetails/PurchaseDetail') as T(X)

cette combinaison est l'omission de @birdus de /text(), ce qui est superflu, mais ajoute les parenthèses de @Mikael-Eriksson autour du sélecteur d'éléments, pour permettre des sélecteurs d'éléments multiples comme dans mon exemple modifié qui devient:

T.x.value('(PurchasePlace/PurchaseCity)[1]','varchar(50)')

raison pour ceci, ce que quelques uns ont demandé, ce n'est pas que la version de @birdus retourne autre chose qu'un singleton dans l'un des exemples discutés ici, mais que cela . Par Microsoft Docs:

les étapes de localisation, les paramètres de fonction, et les opérateurs qui nécessitent des singletons retourneront une erreur si le compilateur ne peut pas déterminer si un singleton est garanti au moment de l'exécution.

3
répondu pbz 2018-02-27 19:05:03