How can I use with inside apply in sql server? I know another solution without apply. but can we do with outer apply? if 'yes' then how?

Question

Can we use with inside any apply in the SQL server? For traversing all nodes in the XML file I am trying to use outer apply so I can traverse in one go.

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM XMLwithOpenXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT CustomerID, CustomerName, Address
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer')
WITH 
(
CustomerID [varchar](50) '@CustomerID',
CustomerName [varchar](100) '@CustomerName',
Address [varchar](100) 'Address'
)
OUTER APPLY
(SELECT OrderDate, OrderID--, Address
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer/Orders/Order')
WITH 
(
OrderDate [varchar](100) '@OrderDate',
OrderID [varchar](100) '@OrderID'
Address [varchar](100) 'Address'
) as Orders
OUTER APPLY
(SELECT Quantity, ProductID--, Address
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer/Orders/Order/OrderDetail')
WITH 
(
Quantity [varchar](100) '@Quantity',
ProductID [varchar](100) '@ProductID'
Address [varchar](100) 'Address'
) as OrderDetail

this is the XML file

<ROOT>
  <Customers>
    --root/customers/cusomer/orders/order/OrderDetail
    <Customer CustomerName="Arshad Ali" CustomerID="C001">
      <Orders>
        <Order OrderDate="2012-07-04T00:00:00" OrderID="10248">
          <OrderDetail Quantity="5" ProductID="10"/>
          <OrderDetail Quantity="12" ProductID="11"/>
          <OrderDetail Quantity="10" ProductID="42"/>
        </Order>
      </Orders>
      <Address> Address line 1, 2, 3</Address>
    </Customer>
  </Customers>
</ROOT>

and this is how i want my result.

CustomerID	CustomerName	Address	OrderID	OrderDate	ProductID	Quantity
C001	Arshad Ali	Address line 1, 2, 3	10248	2012-07-04 00:00:00.000	10	5
C001	Arshad Ali	Address line 1, 2, 3	10248	2012-07-04 00:00:00.000	11	12
C001	Arshad Ali	Address line 1, 2, 3	10248	2012-07-04 00:00:00.000	42	10

I know another solution without apply. but can we do with outer apply? if 'yes' then how? I am new with this so please help me.

Answer 1

To do this, you can choose to go the deepest level and then "crawl up" from it.

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = N'<ROOT>
  <Customers>
    --root/customers/cusomer/orders/order/OrderDetail
    <Customer CustomerName="Arshad Ali" CustomerID="C001">
      <Orders>
        <Order OrderDate="2012-07-04T00:00:00" OrderID="10248">
          <OrderDetail Quantity="5" ProductID="10"/>
          <OrderDetail Quantity="12" ProductID="11"/>
          <OrderDetail Quantity="10" ProductID="42"/>
        </Order>
      </Orders>
      <Address> Address line 1, 2, 3</Address>
    </Customer>
  </Customers>
</ROOT>'

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


select *
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer/Orders/Order/OrderDetail')
WITH
    (
        CustomerID      NVARCHAR(30)    '../../../../Customer/@CustomerID'
    ,   CustomerName    NVARCHAR(100)   '../../../../Customer/@CustomerName'
    ,   Address         NVARCHAR(100)   '../../../Address'
    ,   OrderID         NVARCHAR(30)    '../@OrderID'
    ,   OrderDate       DATETIME    '../@OrderDate'
    ,   ProductID       INT '@ProductID'
    ,   Quantity        INT '@Quantity'

    )
    
exec sp_xml_removedocument  @hDoc OUTPUT -- Always clean up your XMLs!

Edit #2: "real" apply version:

DECLARE @XML AS XML

SELECT  @XML = N'<ROOT>
  <Customers>
    --root/customers/cusomer/orders/order/OrderDetail
    <Customer CustomerName="Arshad Ali" CustomerID="C001">
      <Orders>
        <Order OrderDate="2012-07-04T00:00:00" OrderID="10248">
          <OrderDetail Quantity="5" ProductID="10"/>
          <OrderDetail Quantity="12" ProductID="11"/>
          <OrderDetail Quantity="10" ProductID="42"/>
        </Order>
      </Orders>
      <Address> Address line 1, 2, 3</Address>
    </Customer>
  </Customers>
</ROOT>'


SELECT  cu.value('@CustomerName', 'nvarchar(1000)') name
,   cu.value('@CustomerID', 'nvarchar(100)') id
,   cu.value('Address[1]', 'NVARCHAR(1000)') address
,   oo.value('@OrderDate', 'datetime') orderdate
,   oo.value('@OrderID', 'varchar(30)') orderid
,   ood.value('@Quantity', 'int') qty
,   ood.value('@ProductID', 'varchar(10)') productId
FROM    (
    SELECT  @xml AS x
    ) xml
OUTER APPLY x.nodes('ROOT/Customers/Customer') c(cu)
OUTER APPLY cu.nodes('Orders/Order') o(oo)
OUTER APPLY oo.nodes('OrderDetail') od(ood)