Path: csiph.com!eternal-september.org!feeder.eternal-september.org!reader01.eternal-september.org!.POSTED!not-for-mail
From: Erland Sommarskog <esquel@sommarskog.se>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: Unable to query xml data in sql server
Date: Tue, 04 Aug 2020 21:29:35 +0200
Organization: Erland Sommarskog
Lines: 59
Message-ID: <XnsAC0FDAA3923CAYazorman@127.0.0.1>
References: <b8df0410-a799-45e8-b128-4c8a62b38ed6o@googlegroups.com> <25356606-299b-4623-809d-0d6eb9af0906o@googlegroups.com> <XnsABD8F11BF16BFYazorman@127.0.0.1> <4b1c3504-2511-4c3c-a5fb-b880e39caeb6o@googlegroups.com> <XnsABD928EDA5F3Yazorman@127.0.0.1> <07f4ed04-6ddf-42e8-afac-655b93b690e3o@googlegroups.com> <XnsABD9D6547A1DYazorman@127.0.0.1> <e9abcea1-90f7-4055-8a21-1569c4d6e03ao@googlegroups.com> <XnsABDB6BDB76048Yazorman@127.0.0.1> <1bed2a65-5433-4937-8341-754e4bfe4af8o@googlegroups.com> <XnsABE056767CA18Yazorman@127.0.0.1> <15fe54f8-bced-4ff8-a6ba-5ead217270a5o@googlegroups.com> <XnsABE169CB87F1BYazorman@127.0.0.1> <de85ea65-58d9-4359-9be6-0db185fe83c9o@googlegroups.com> <XnsABECD21E350A5Yazorman@127.0.0.1> <4a865c7f-1a4f-4398-9150-36195d16012co@googlegroups.com> <XnsABFA78473A378Yazorman@127.0.0.1> <bc09787e-6d86-4b0a-8df7-965c6432e956o@googlegroups.com> <XnsABFACB4EBCD2CYazorman@127.0.0.1> <f73f13a3-0171-45eb-989a-996b8f0e799eo@googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Injection-Info: reader02.eternal-september.org; posting-host="e6ad9dad5b0a17083e9d58a0637a4937"; logging-data="11737"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX18cPcpZiKUpd03JW5BWDxTQ"
User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock: sha1:fUwvK/pdKw9TmXKhQ5Ns0bsNgLI=
Xref: csiph.com comp.databases.ms-sqlserver:2074

chris (chris.sjpd@gmail.com) writes:
> When I run the below query, it has no errors but returns no data. Could
> you please take a look at my query & let me know what I'm missing? 
> Appreciate your helps very much. 
> 
> SELECT Agency.a.value('(AccidentReportNumber-AllPages/text())[1]', 'varchar(20)') AS [AccidentReportNumber],     
>        Vehicle.v.value('(Make/text())[1]', 'varchar(20)') AS [Make],       
>      Myrole.r.value('(TrafficUnitDriverCheck/text())[1]', 'nvarchar(10)') AS [Driver],        
>      Myrole.r.value('(SobrietyDrugPhysicalA/text())[1]', 'varchar(20)') AS [Sobriety_Drug_Physical_A]
>         
>  FROM xmltbl
>  CROSS APPLY xmlcontent.nodes('/agency') AS Agency(a)
>  CROSS  APPLY Agency.a.nodes('TrafficUnit[TrafficUnit_Number="2"]/Driver') AS Driver(d)
>  CROSS APPLY Agency.a.nodes('TrafficUnit/TrafficUnit_Number/Driver/Vehicle') AS Vehicle(v)   
>  CROSS APPLY Agency.a.nodes('/TrafficUnit') AS Myrole(r) 
> 

What I can see directly is that you are not missing anything, but you 
are having one too many. This line:

>  CROSS APPLY Agency.a.nodes('/TrafficUnit') AS Myrole(r) 

Should be

  CROSS APPLY Agency.a.nodes('TrafficUnit') AS Myrole(r) 

I have learnt the hard way that if you have a leading slash in call to nodes
in CROSS APPLY, you don't get anything back.

A general tip with this query is to change CROSS to OUTER for one or more 
until you get row back. With OUTER APPLY the rows in the left table are 
retained. This way, you can figure out which of the CROSS APPLY that 
is giving you problems.

Then you will have to take it from there. If I look at your post from
June 11th, it seems that the structure is:

 <TrafficUnit>
    <TrafficUnit_Number>1</TrafficUnit_Number>
    <Driver>
       ...
    </Driver>
    <Vehicle>
       ...
    </Vehicle>

But the second CROSS APPLY is written for


 <TrafficUnit>
    <TrafficUnit_Number>
       <Driver>
          <Vehicle>
           ...
          </Vechicle>
       </Driver>
    </TraficUnit_Number>