Groups > comp.databases.ms-sqlserver > #2074

Re: Unable to query xml data in sql server

From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Unable to query xml data in sql server
Date	2020-08-04 21:29 +0200
Organization	Erland Sommarskog
Message-ID	<XnsAC0FDAA3923CAYazorman@127.0.0.1> (permalink)
References	(15 earlier) <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>

Show all headers | View raw

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>

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

Unable to query xml data in sql server chris H <chris.sjpd@gmail.com> - 2020-06-10 09:29 -0700
  Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-06-10 21:55 +0200
    Re: Unable to query xml data in sql server chris H <chris.sjpd@gmail.com> - 2020-06-10 13:05 -0700
      Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-06-10 23:42 +0200
        Re: Unable to query xml data in sql server chris H <chris.sjpd@gmail.com> - 2020-06-10 14:50 -0700
          Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-06-11 00:15 +0200
            Re: Unable to query xml data in sql server chris H <chris.sjpd@gmail.com> - 2020-06-10 15:30 -0700
              Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-06-11 21:04 +0200
                Re: Unable to query xml data in sql server chris <chris.sjpd@gmail.com> - 2020-06-12 15:54 -0700
                Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-06-13 10:36 +0200
                Re: Unable to query xml data in sql server chris <chris.sjpd@gmail.com> - 2020-06-17 13:13 -0700
                Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-06-18 08:29 +0200
                Re: Unable to query xml data in sql server chris <chris.sjpd@gmail.com> - 2020-06-18 16:30 -0700
                Re: Unable to query xml data in sql server chris <chris.sjpd@gmail.com> - 2020-06-18 16:47 -0700
                Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-06-19 10:24 +0200
                Re: Unable to query xml data in sql server chris <chris.sjpd@gmail.com> - 2020-06-29 16:56 -0700
                Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-06-30 20:39 +0200
                Re: Unable to query xml data in sql server chris <chris.sjpd@gmail.com> - 2020-07-13 14:25 -0700
                Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-07-14 11:49 +0200
                Re: Unable to query xml data in sql server chris <chris.sjpd@gmail.com> - 2020-07-14 09:56 -0700
                Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-07-14 19:59 +0200
                Re: Unable to query xml data in sql server chris <chris.sjpd@gmail.com> - 2020-07-15 15:20 -0700
                Re: Unable to query xml data in sql server chris <chris.sjpd@gmail.com> - 2020-08-04 09:17 -0700
                Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-08-04 21:29 +0200

csiph-web