Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.databases.ms-sqlserver > #2074
Show key headers only | 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
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