MS SQL XML - What is the sence behind nodes-method and the cross apply!

Author

What is the sence behind nodes-method and the cross apply!

nullstring

2005-09-19, 7:23 am

I tried some examples, but I can't found any 'working' examples!?!
All run on an error!

What is Cross Apply? Do somebody have an example for this??
(I think, that a kind of (inner) join!?
But how does it works???

thanx in advance....

Eugene Kogan [MSFT]

2005-09-19, 8:24 pm

CROSS APPLY is an extension of CROSS JOIN where on the right side you can
bind to the table from the left side.
And CROSS JOIN with WHERE clause is equivalent to INNER JOIN.
Details can be found in BOL in FROM clause documentation.

Below are 3 equivalent queries with CROSS APPLY, CROSS JOIN, and INNER JOIN:

-- 1)
SELECT *
FROM a CROSS APPLY
(SELECT * FROM b WHERE b.id_a=a.id) bb
-- 2)
SELECT * FROM a INNER JOIN b ON b.id_a=a.id
-- 3)
SELECT * FROM a CROSS JOIN b WHERE b.id_a=a.id

CROSS APPLY is an essential syntax element for Table Valued Function
invocation in SQL Server. Here's an example

SELECT * FROM tbl CROSS APPLY MyTVF(tbl.col1) MyTvfTbl

See in BOL: Accessing and Changing Database Data > Query Fundamentals >
Using the FROM Clause > Using APPLY
and the example Adam gave you on another thread.

Since nodes() method on XML type is simply a table valued function you'd
have to use CROSS APPLY if nodes() method needs to be applied to multiple
XML instances.
See in BOL: Using XML in SQL Server > xml Data Type > xml Data Type Methods
> nodes() Method (xml Data Type)
Here's another example:

CREATE TABLE t(id INT PRIMARY KEY, x XML)
GO
INSERT t VALUES
(1, N'<root><elt attr="val1"/><elt attr="val2"/></root>')
GO
INSERT t VALUES
(2, N'<root><elt attr="val3"/></root>')
GO
-- Extract values of all "attr" attributes each in a separate row
SELECT xml_ref.value('.','nvarchar(1000)')
FROM t CROSS APPLY t.x.nodes('/root/elt/@attr') tbl(xml_ref)
GO

Regards,
Eugene
---
This posting is provided "AS IS" with no warranties, and confers no rights.

"nullstring" <johannes.veit@datapec.de> wrote in message
news:1127131631.673114.9720@g47g2000cwa.googlegroups.com...
>I tried some examples, but I can't found any 'working' examples!?!
> All run on an error!
>
> What is Cross Apply? Do somebody have an example for this??
> (I think, that a kind of (inner) join!?
> But how does it works???
>
> thanx in advance....
>

nullstring

2005-09-20, 3:24 am

Wow! That is great! I searched for an example like this, but couldn't
find one:)

Now I tried to make this with my own exampledatas, but I'm getting this
errror:
Msg 208, Level 16, State 1, Line 2
Invalid object name tab.col.nodes'.

with this query:
select xmlref.value('.','nvarchar(1000)')
from tab cross apply tab.col.nodes('/root/members/@Id'), tbl(xmlref)

what could be the mistake?

thanks a lot, this is really helpful....

greez....

nullstring

2005-09-20, 7:23 am

Oh, now it's working!
Don't know why, but it doesn't matter!!

But is it possible to show some attributes of the xml-col also as a col
in the resultset????
for example (depending on your example), that i have
In the the record with id=1
<root>
<elt attr1="val1" attr2="Some Text"/>
<elt attr1="val2" attr2="Again some Text"/>
</root>
and the second record (id=2)
<root>
<elt attr1="val2" attr2="Not again some Text"/>
</root>

In the Result I will have the cols 'id', 'attr1', 'attr2' !!

greez...

Han

2005-09-20, 7:23 am

Yes, if I understand your question properly. Something like,

select
id col1,
a.b.value('.', 'varchar(100)') col2,
a.b.value('./../@attr2', 'varchar(100)') col3
from t
cross apply t.x.nodes('/ descendant::@attr1')
a(b)

The result should be three rows. Not tested. Why not post some DDL script
for us to test?

--
Pohwan Han. Seoul. Have a nice day.
"nullstring" <johannes.veit@datapec.de> wrote in message
news:1127210315.151334.205780@g14g2000cwa.googlegroups.com...
> Oh, now it's working!
> Don't know why, but it doesn't matter!!
>
> But is it possible to show some attributes of the xml-col also as a col
> in the resultset????
> for example (depending on your example), that i have
> In the the record with id=1
> <root>
> <elt attr1="val1" attr2="Some Text"/>
> <elt attr1="val2" attr2="Again some Text"/>
> </root>
> and the second record (id=2)
> <root>
> <elt attr1="val2" attr2="Not again some Text"/>
> </root>
>
> In the Result I will have the cols 'id', 'attr1', 'attr2' !!
>
>
>
> greez...
>

nullstring

2005-09-20, 7:23 am

No problem! Here we go:
---------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE tab(id INT PRIMARY KEY, col XML)
GO
INSERT tab VALUES
(1, N'<root><elt attr1="val1" attr2="valA"/><elt attr1="val2"
attr2="valB"/></root>')
GO
INSERT tab VALUES
(2, N'<root><elt attr1="val3" attr2="valC"/></root>')
GO
-- Extract values of all "attr" attributes each in a separate row
SELECT id, xml_ref.value('.','nvarchar(1000)') as result
FROM tab CROSS APPLY tab.col.nodes('/root/elt/@attr1') tbl(xml_ref)
GO
---------------------------------------------------------------------------------------------------------------------------------------

The query returns two columns ('id' and 'result' (<-the value of
'attr1'))
But I want to have the value of 'attr2' in the select two, as a third
column!!!!

thanx and greez......

Han

2005-09-20, 9:23 am

See if this is what you mean,

SELECT id, xml_ref.value('.','nvarchar(1000)') as result,
xml_ref.value('./../@attr2', 'nvarchar(max)') as result2
FROM tab CROSS APPLY tab.col.nodes('/root/elt/@attr1') tbl(xml_ref)

--
Pohwan Han. Seoul. Have a nice day.
"nullstring" <johannes.veit@datapec.de> wrote in message
news:1127218505.818680.314080@g47g2000cwa.googlegroups.com...
> No problem! Here we go:
> ---------------------------------------------------------------------------------------------------------------------------------------
> CREATE TABLE tab(id INT PRIMARY KEY, col XML)
> GO
> INSERT tab VALUES
> (1, N'<root><elt attr1="val1" attr2="valA"/><elt attr1="val2"
> attr2="valB"/></root>')
> GO
> INSERT tab VALUES
> (2, N'<root><elt attr1="val3" attr2="valC"/></root>')
> GO
> -- Extract values of all "attr" attributes each in a separate row
> SELECT id, xml_ref.value('.','nvarchar(1000)') as result
> FROM tab CROSS APPLY tab.col.nodes('/root/elt/@attr1') tbl(xml_ref)
> GO
> ---------------------------------------------------------------------------------------------------------------------------------------
>
> The query returns two columns ('id' and 'result' (<-the value of
> 'attr1'))
> But I want to have the value of 'attr2' in the select two, as a third
> column!!!!
>
>
>
> thanx and greez......
>

nullstring

2005-09-20, 11:23 am

Yes, this is exactly what I search!!!

Thank you very much!!!!!

Eugene Kogan [MSFT]

2005-09-20, 8:24 pm

In case of

CREATE TABLE tab(id INT PRIMARY KEY, col XML)
GO
INSERT tab VALUES
(1, N'<root><elt attr1="val1" attr2="valA"/><elt attr1="val2"
attr2="valB"/></root>')
GO
-- an instance with no att2
INSERT tab VALUES
(2, N'<root><elt attr2="valC"/></root>')
GO

you may normally want to produce a row for each "elt" element and project a
column for each of "att1" and "attr2" with NULL value if there's no
attribute. In this case the query would be:

SELECT
id,
xml_ref. value('@attr1','nvar
char(1000)') AS result,
xml_ref.value('@attr2', 'nvarchar(max)') AS result2
FROM tab CROSS APPLY
tab.col.nodes('/root/elt') tbl(xml_ref)

which is different from

SELECT
id,
xml_ref.value('.','nvarchar(1000)') AS result,
xml_ref.value('./../@attr2', 'nvarchar(max)') AS result2
FROM tab CROSS APPLY
tab.col.nodes('/root/elt/@attr1') tbl(xml_ref)

Best regards,
Eugene
---
This posting is provided "AS IS" with no warranties, and confers no rights.

"nullstring" <johannes.veit@datapec.de> wrote in message
news:1127227243.218696.32040@g49g2000cwa.googlegroups.com...
> Yes, this is exactly what I search!!!
>
> Thank you very much!!!!!
>

Han

2005-09-21, 3:24 am

Exactly. What Eugene means is performance/readibility issue. Search from top
to bottom or parent to children. Not from bottom to top or children to
parent.

--
Pohwan Han. Seoul. Have a nice day.
"Eugene Kogan [MSFT]" <ekogan@online.microsoft.com> wrote in message
news:e8znyXivFHA.3124@TK2MSFTNGP12.phx.gbl...
> In case of
>
> CREATE TABLE tab(id INT PRIMARY KEY, col XML)
> GO
> INSERT tab VALUES
> (1, N'<root><elt attr1="val1" attr2="valA"/><elt attr1="val2"
> attr2="valB"/></root>')
> GO
> -- an instance with no att2
> INSERT tab VALUES
> (2, N'<root><elt attr2="valC"/></root>')
> GO
>
> you may normally want to produce a row for each "elt" element and project
> a column for each of "att1" and "attr2" with NULL value if there's no
> attribute. In this case the query would be:
>
> SELECT
> id,
> xml_ref. value('@attr1','nvar
char(1000)') AS result,
> xml_ref.value('@attr2', 'nvarchar(max)') AS result2
> FROM tab CROSS APPLY
> tab.col.nodes('/root/elt') tbl(xml_ref)
>
> which is different from
>
> SELECT
> id,
> xml_ref.value('.','nvarchar(1000)') AS result,
> xml_ref.value('./../@attr2', 'nvarchar(max)') AS result2
> FROM tab CROSS APPLY
> tab.col.nodes('/root/elt/@attr1') tbl(xml_ref)
>
> Best regards,
> Eugene
> ---
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
> "nullstring" <johannes.veit@datapec.de> wrote in message
> news:1127227243.218696.32040@g49g2000cwa.googlegroups.com...
>
>

nullstring

2005-09-21, 3:24 am

So you can ensure that only nodes were selected, which contain the same
attributes ?!!?
Is that right???

It's a kind of inner join! well, not referring to the content of a
record (as inner join do), but referring to the metainformation!
Is that right too??

really exciting:)

thanx and greez.....

Eugene Kogan [MSFT]

2005-09-21, 8:24 pm

It's not about a join type. And not about rewriting a query for better
performance.
The example I gave was to illustrate that XPath expressions /root/elt/@attr1
and /root/elt may return different number of nodes. If you run the queries
in my last post in this thread you'll see that the first one returns 3 rows
and the second - only 2. The first query gives you the semantics you would
likely want.

Here are the queries again:

CREATE TABLE tab(id INT PRIMARY KEY, col XML)
GO
INSERT tab VALUES
(1, N'<root><elt attr1="val1" attr2="valA"/><elt attr1="val2"
attr2="valB"/></root>')
GO
-- an instance with no "attr1"
INSERT tab VALUES
(2, N'<root><elt attr2="valC"/></root>')
GO

-- query 1 - returns 3 rows
SELECT
id,
xml_ref. value('@attr1','nvar
char(1000)') AS result,
xml_ref.value('@attr2', 'nvarchar(max)') AS result2
FROM tab CROSS APPLY
tab.col.nodes('/root/elt') tbl(xml_ref)

-- query 2 - returns 2 rows
SELECT
id,
xml_ref.value('.','nvarchar(1000)') AS result,
xml_ref.value('./../@attr2', 'nvarchar(max)') AS result2
FROM tab CROSS APPLY
tab.col.nodes('/root/elt/@attr1') tbl(xml_ref)

Best regards,
Eugene
---
This posting is provided "AS IS" with no warranties, and confers no rights.

"nullstring" <johannes.veit@datapec.de> wrote in message
news:1127286169.460966.32290@g49g2000cwa.googlegroups.com...
> So you can ensure that only nodes were selected, which contain the same
> attributes ?!!?
> Is that right???
>
> It's a kind of inner join! well, not referring to the content of a
> record (as inner join do), but referring to the metainformation!
> Is that right too??
>
> really exciting:)
>
>
> thanx and greez.....
>

Sponsored Links