RE: XML Cross Apply ...

Tech-Archive recommends: Fix windows errors by optimizing your registry

Yep OUTER APPLY is a bit like a LEFT JOIN.

HTH

"WCM" wrote:

Hi Bob,

I believe that the discrepancy between your query return and mine is due to
the fact that for exactly 8,134 records [Reccipient] and [Recipient2] and
[Addr] columns are null. Doesn't this mean that those records will not be
returned by CROSS APPLY, even though there is a non-null value in
[NetAmount]?

So in your query, wouldn't you need to use OUTER APPLY instead of CROSS
APPLY so that null values are included in the return? When I replace CROSS
APPLY in your query with OUTER APPLY, your query and my query return
precisely the same results. Your query however, runs 8 times faster than
mine!

Thanks ...

Bill




"Bob" wrote:

You should avoid excessive use of the parent axis (..) as documented here:

http://blogs.technet.com/wardpond/archive/2006/02/19/database-programming-applying-apply-solving-parent-axis-access-performance-issues-in-xml-access-in-sql-server-2005.aspx

Try something like this:

SELECT
rec,
[fileName],
Shipment.col.value('(Package/TrackingNumber)[1]', 'nvarchar(20)') AS
TrackingNumber,
Shipment.col.value('(TransactionDateCCYYMMDD)[1]', 'nvarchar(20)') AS
TransDate,
ReceiverAddress.col.value('(Addressee/Name)[1]', 'varchar(100)') AS
Recipient,
ReceiverAddress.col.value('(Attention/Name)[1]', 'varchar(100)') AS
Recipient2,
ReceiverAddress.col.value('(Address/StreetAddress)[1]', 'varchar(100)') AS
Addr,
ReceiverAddress.col.value('(Address/CityName)[1]', 'varchar(100)') AS City,
ReceiverAddress.col.value('(Address/StateCode)[1]', 'varchar(100)') AS State,
ReceiverAddress.col.value('(Address/PostalCode)[1]', 'varchar(100)') AS
PostCode,
NetAmount.col.value('.', 'DECIMAL(10,4)') AS NetAmount
--into xmlT
FROM noder
CROSS APPLY noder.theNode.nodes( 'Shipment' ) AS Shipment(col)
CROSS APPLY Shipment.col.nodes('AddressDetails/ReceiverAddress') AS
ReceiverAddress(col)
CROSS APPLY
Shipment.col.nodes('Package/ChargeDetails/Charge/ChargeInformation/NetAmount') AS NetAmount(col)





"WCM" wrote:

Sql Server 2005:

The following tsql works fine, but I need to know if it is documented
syntax.

My reason for asking: I thought that the "Apply" operator allows you to
invoke a table-valued function for each row returned by an outer table
expression. But in the following example, it appears that the reverse is
happening: the outer expression is being applied to each row returned by the
nodes() method.

In the following code 'noder' is a table that contains an xml column named
'theNode" - the nodes method is used to shred that column into several rows
for each record in the noder table.

Thanks in advance for your response ...

Bill

********************

SELECT rec, [fileName],
T.NetAmount.value('(../../../../TrackingNumber)[1]', 'nvarchar(20)') as
TrackingNumber,
T.NetAmount.value('(../../../../../TransactionDateCCYYMMDD)[1]',
'nvarchar(20)') as TransDate,

T.NetAmount.value('(../../../../../AddressDetails/ReceiverAddress/Addressee/Name)[1]', 'varchar(100)') as Recipient,

T.NetAmount.value('(../../../../../AddressDetails/ReceiverAddress/Attention/Name)[1]', 'varchar(100)') as Recipient2,

T.NetAmount.value('(../../../../../AddressDetails/ReceiverAddress/Address/StreetAddress)[1]', 'varchar(100)') as Addr,

T.NetAmount.value('(../../../../../AddressDetails/ReceiverAddress/Address/CityName)[1]', 'varchar(100)') as City,

T.NetAmount.value('(../../../../../AddressDetails/ReceiverAddress/Address/StateCode)[1]', 'varchar(100)') as State,

T.NetAmount.value('(../../../../../AddressDetails/ReceiverAddress/Address/PostalCode)[1]', 'varchar(100)') as PostCode,
T.NetAmount.value('.', 'decimal(10,4)') AS NetAmount
into xmlT
FROM noder
cross apply
noder.theNode.nodes('Shipment/Package/ChargeDetails/Charge/ChargeInformation/NetAmount')
AS T(NetAmount);

**************************
.

Relevant Pages

  • RE: XML Cross Apply ...
    ... "The APPLY operator allows you to invoke a table-valued function for each ... row returned by an outer table expression of a query. ... CROSS APPLY allows us to use the nodes method (or ... I believe that the discrepancy between your query return and mine is due to ...
    (microsoft.public.sqlserver.xml)
  • RE: XML Cross Apply ...
    ... "WCM" wrote: ... the nodesmethod is like a derived table, so that "cross apply" or "outer ... So in your query, wouldn't you need to use OUTER APPLY instead of CROSS ...
    (microsoft.public.sqlserver.xml)
  • RE: XML Cross Apply ...
    ... the nodesmethod is like a derived table, so that "cross apply" or "outer ... So in your query, wouldn't you need to use OUTER APPLY instead of CROSS ... In the following code 'noder' is a table that contains an xml column named ...
    (microsoft.public.sqlserver.xml)
  • RE: XML Cross Apply ...
    ... behaves with cross apply is where I'm a little fuzzy. ... So in your query, wouldn't you need to use OUTER APPLY instead of CROSS ... In the following code 'noder' is a table that contains an xml column named ...
    (microsoft.public.sqlserver.xml)
  • RE: XML Cross Apply ...
    ... So in your query, wouldn't you need to use OUTER APPLY instead of CROSS ... In the following code 'noder' is a table that contains an xml column named ...
    (microsoft.public.sqlserver.xml)