Multi level Cross apply - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multi level Cross apply

Author

Topic

ashw1984
Starting Member

6 Posts

Posted - 2009-09-21 : 10:56:26

Hi there the code below gives these results

(1 row(s) affected)
ID Name mAKE model
------------------------------ ------------------------------ ------------------------------ ------------------------------
woman notsosuper VOLKSWAGEN POLO E (55)
woman notsosuper Renault CLIO
Woman Super VOLKSWAGEN POLO E (55)
Woman Super Renault CLIO

(4 row(s) affected)

i would like these results

ID Name mAKE model
------------------------------ ------------------------------ ------------------------------ ------------------------------
woman notsosuper VOLKSWAGEN POLO E (55)
Woman Super Renault CLIO

can somebody please help on this

in this example i only have two person elements
but when i run the code live i will have 1000's

  drop table #tmpa
 select
		cast('<root>
		  <person>
			<VRN>aa00aaa</VRN>
			<Make>VOLKSWAGEN   </Make>
			<Model>POLO E (55)</Model>
			<PolicyNumber>987654321</PolicyNumber>
			<Namedet>
			  <Title>Miss</Title>
			  <FirstName>notsosuper</FirstName>
			  <LastName>woman</LastName>
			  <PostCode>bbb111</PostCode>
			</Namedet>
		  </person>
		  <person>
			<VRN>bb99bbb</VRN>
			<Make>Renault</Make>
			<Model>CLIO</Model>
			<PolicyNumber>123456789</PolicyNumber>
			<Namedet>
			  <Title>Miss</Title>
			  <FirstName>Super</FirstName>
			  <LastName>Woman</LastName>
			  <PostCode>zzz123</PostCode>
			</Namedet>
		  </person>
		</root>' as xml) as test
	into #tmpa
----
	SELECT
		x.l.value('LastName[1]','VARCHAR(30)') AS [ID],
		x.l.value('FirstName[1]','VARCHAR(30)') AS [Name],
		y.m.value('Make[1]','VARCHAR(30)') AS mAKE,
		y.m.value('Model[1]','VARCHAR(30)') AS model
	  FROM #tmpa
	 CROSS APPLY test.nodes('/root/person/Namedet') x(l)
	 CROSS APPLY test.nodes('/root/person') y(m)

ASH

ashw1984
Starting Member

6 Posts

Posted - 2009-09-22 : 03:40:32

Basically to sum this up. I need one line per 'person' element with the details stated.

hope this explains it better

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-22 : 05:58:41

Can you try this?

SELECT
X.ID,X.Name,Y.mAKE,Y.model
FROM
(
Select
y.m.value('Make[1]','VARCHAR(30)') AS mAKE,
y.m.value('Model[1]','VARCHAR(30)') AS model,
row_number() over(order by (select 1)) as row_ID
from
#tmpa
CROSS APPLY test.nodes('/root/person') y(m)
)Y

INNER JOIN

(
SELECT
x.l.value('LastName[1]','VARCHAR(30)') AS [ID],
x.l.value('FirstName[1]','VARCHAR(30)') AS [Name],
row_number() over(order by (select 1)) as row_ID
FROM
#tmpa
CROSS APPLY test.nodes('/root/person/Namedet') x(l)
)X
ON
Y.row_ID=X.row_ID

ashw1984
Starting Member

6 Posts

Posted - 2009-09-22 : 06:54:41

That is fantastic. THANK YOU THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

ashw1984
Starting Member

6 Posts

Posted - 2009-09-22 : 07:05:19

WHOOPS SORRY.
THATS GREAT BUT I HAVE MORE ISSUES
imagine this is the code


drop table #tmpa
 select
		cast('<root>
		  <person>
			<VRN>aa00aaa</VRN>
			<Make>VOLKSWAGEN   </Make>
			<Model>POLO E (55)</Model>
			<PolicyNumber>987654321</PolicyNumber>
			<Namedet>
			  <Title>Miss</Title>
			  <FirstName>notsosuper</FirstName>
			  <LastName>woman</LastName>
			  <PostCode>bbb111</PostCode>
			</Namedet>
		  </person>
		  <person>
			<VRN>aa00aaa</VRN>
			<Make>VOLKSWAGEN   xxxx</Make>
			<Model>POLO E (55)xxxx</Model>
			<PolicyNumber>987654321</PolicyNumber>
			<Namedet>
			  <Title>Miss</Title>
			  <FirstName>notsosuperxxxx</FirstName>
			  <LastName>womanxxxx</LastName>
			  <PostCode>bbb111</PostCode>
			</Namedet>
			<Namedet>
			  <Title>Miss</Title>
			  <FirstName>mr</FirstName>
			  <LastName>man</LastName>
			  <PostCode>bbb111</PostCode>
			</Namedet>
		  </person>
		  <person>
			<VRN>bb99bbb</VRN>
			<Make>Renaultzzz</Make>
			<Model>CLIOzzz</Model>
			<PolicyNumber>123456789</PolicyNumber>
			<Namedet>
			  <Title>Miss</Title>
			  <FirstName>Superzz</FirstName>
			  <LastName>Womanzz</LastName>
			  <PostCode>zzz123</PostCode>
			</Namedet>
		  </person>
		</root>' as xml) as test
	into #tmpa

ID Name mAKE model
------------------------------ ------------------------------ ------------------------------ ------------------------------
woman notsosuper VOLKSWAGEN POLO E (55)
womanxxxx notsosuperxxxx VOLKSWAGEN xxxx POLO E (55)xxxx
man mr Renaultzzz CLIOzzz

This is the result
but it should be

ID Name mAKE model
------------------------------ ------------------------------ ------------------------------ ------------------------------
woman notsosuper VOLKSWAGEN POLO E (55)
womanxxxx notsosuperxxxx VOLKSWAGEN xxxx POLO E (55)xxxx
man mr VOLKSWAGEN xxxx POLO E (55)xxxx
Superzz Womanzz Renaultzzz CLIOzzz

Sorry to keep this going
If anybody has another way of doing this i am very welcome to other solutions that use other parts of sql if there are better options

ASH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources