MS SQL Server
  1. MS SQL Server
  3. SQL Server – requête datatables à partir du file XML
Intereting Posts
Déterminer par programme si le server lié est Oracle ou SQL Server Filtrer les loggings d'une table en fonction de ses autres loggings Pivot SQL avec colonnes générées dynamics et fonction d'agrégat MAX de SUM et utilisation de TOP dans les vues Est-ce que Count (*) peut returnner null? Résultats étranges de la sous-requête NOT IN Alter Table Drop Column a échoué Stocker le nom de file du file téléchargé dans la database Java Complicated SQL Join Pourquoi SCOPE_IDENTITY renvoie NULL? La memory insuffisante sur le server IIS peut-elle entraîner des timeouts d'attente SQL (SQL Server dans une zone distincte)? Détecter la cause du locking de mise à jour SQL Server En utilisant Dapper.net pour appeler une procédure stockée, returnnez toujours -1 Taille maximale de varchar (max) dans SQL Server 2000 Comment puis-je savoir si le code de triggersment est en cours d'exécution sur un abonné de réplication?

SQL Server – requête datatables à partir du file XML

Je suis nouveau aux choses XML. J'ai compris comment interroger et renvoyer les valeurs du file XML (exemple ci-dessous). Cependant, je rencontre un problème qui ne capture que le premier noeud de la balise 'SerialNo' car le tag a le même nom de noeud "SerialNo" répété. Dans le file XML, il a 4 numéros de série pour SKU # TT234343, mais il me donne seulement le premier Serial11111. Je suis totalement coincé et je ne sais pas comment listr tous ces numéros de série.

Je voudrais le résultat de la requête pour SKU # TT234343, énumérant les 4 numéros de série si possible.

S'il vous plaît aider. Merci!

Le file XML ressemble à ceci: 

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?> <ROOT> <ShipNotice version="1" > <InvoiceDate>01/01/2015</InvoiceDate> <InvoiceNumber>6868686</InvoiceNumber> <ShipDate>02/02/2015</ShipDate> <ShipTime>2306</ShipTime> <PONumber>P444444</PONumber> <PODate>03/03/2015</PODate> <ShipCode>XXX</ShipCode> <ShipDescription>FedEx Economy</ShipDescription> <ShipTo> <AddressName>ShipABC</AddressName> <AddressContact>Name1</AddressContact> <AddressLine1>2222 Street Name</AddressLine1> <AddressLine2> </AddressLine2> <City>AUSTIN</City> <State>TX</State> <ZipCode>78111</ZipCode> </ShipTo> <BillTo> <AddressName>BillABC</AddressName> <AddressContact>Name1</AddressContact> <AddressLine1>1234 Street Name</AddressLine1> <AddressLine2>-SUITE 111</AddressLine2> <City>Los Angeles</City> <State>CA</State> <ZipCode>95136</ZipCode> </BillTo> <TotalWeight>324</TotalWeight> <EmptyCartonWGT>0</EmptyCartonWGT> <NumberOfCarton>1</NumberOfCarton> <DirectShipFlag>D</DirectShipFlag> <ShipFromWarehouse>88</ShipFromWarehouse> <ShipFromZip>94538</ShipFromZip> <ShipTrackNo>33333333</ShipTrackNo> <EndUserPONumber>55555555</EndUserPONumber> <CustomerSONumber/> <Package sequence="1" > <TrackNumber>666666666</TrackNumber> <PackageWeight>324</PackageWeight> <Item sequence="1" > <SOLineNo>1</SOLineNo> <MfgPN>XYZ1111111</MfgPN> <SKU>TT234343</SKU> <ShipQuantity>4</ShipQuantity> <CustPOLineNo>1</CustPOLineNo> <CustSOLineNo/> <Description>Server1234</Description> <CustPN/> <UPC/> <UnitPrice>1000</UnitPrice> <EndUserPOLineNo>0</EndUserPOLineNo> <SerialNo>Serial11111</SerialNo> <SerialNo>Serial22222</SerialNo> <SerialNo>Serial33333</SerialNo> <SerialNo>Serial44444</SerialNo> </Item> <Item sequence="2" > <SOLineNo>2</SOLineNo> <MfgPN>XYZ222222</MfgPN> <SKU>TT8848788</SKU> <ShipQuantity>4</ShipQuantity> <CustPOLineNo>2</CustPOLineNo> <CustSOLineNo/> <Description>GGG localization</Description> <CustPN/> <UPC/> <UnitPrice>0.00</UnitPrice> <EndUserPOLineNo>0</EndUserPOLineNo> <SerialNo/> </Item> </Package> </ShipNotice> </ROOT>

La requête SQL: 

 DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX) EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmlData SELECT InvoiceNumber, PONumber, PODate , AddressName , MfgPN, SerialNo --, AddressContact, AddressLine1, AddressLine2, City, State, ZipCode FROM OPENXML(@hDoc, '/ROOT/ShipNotice/Package/Item') WITH ( --- ################# Level 1 ################# InvoiceNumber [varchar](50) '../../InvoiceNumber', PONumber [varchar](100) '../../PONumber', PODate [varchar](100) '../../PODate', --- ################# Level 2 ################# AddressName [varchar](100) '../../ShipTo/AddressName', --- ################# Level 3 ################# MfgPN [varchar](100) 'MfgPN', SerialNo [varchar](100) 'SerialNo' )

Vous pouvez essayer d'utiliser la nouvelle technologie XQuery au lieu de OPENXML() . En utilisant XQuery, vous pouvez utiliser la méthode nodes() pour décomposer le XML sur des éléments qui correspondront aux lignes de la sortie, et utiliser value() pour extraire la valeur de l'élément: 

 SELECT shipnotice.value('InvoiceNumber[1]','varchar(20)') InvoiceNumber , shipnotice.value('PONumber[1]','varchar(20)') PONumber , shipnotice.value('PODate[1]','varchar(20)') PODate , shipnotice.value('(ShipTo/AddressName)[1]','varchar(100)') AddressName , item.value('MfgPN[1]','varchar(100)') MfgPN , serialno.value('.','varchar(100)') SerialNo FROM @XML.nodes('/ROOT/ShipNotice') as t(shipnotice) OUTER APPLY shipnotice.nodes('Package/Item') as t2(item) OUTER APPLY item.nodes('SerialNo') as t3(serialno)

Sqlfiddle Demo

sortie: 

 | InvoiceNumber | PONumber | PODate | AddressName | MfgPN | SerialNo | |---------------|----------|------------|-------------|------------|-------------| | 6868686 | P444444 | 03/03/2015 | ShipABC | XYZ1111111 | Serial11111 | | 6868686 | P444444 | 03/03/2015 | ShipABC | XYZ1111111 | Serial22222 | | 6868686 | P444444 | 03/03/2015 | ShipABC | XYZ1111111 | Serial33333 | | 6868686 | P444444 | 03/03/2015 | ShipABC | XYZ1111111 | Serial44444 | | 6868686 | P444444 | 03/03/2015 | ShipABC | XYZ222222 | |