Deklaracja zmiennej + Połączenie tabel + wyciąganie danych z XML SQL

Mam problem z zadeklarowaniem zmiennych w zapytaniu.
Mam zapytanie wyciągające ważne dla mnie dane zapisane w postaci XML

DECLARE @x XML, @z int
set @z = 1001
SELECT @x = (SELECT CAST(kolumnaxml as XML) FROM xxx where xxx.id = @z FOR XML AUTO)
select  x.id, x.ONS, x.Direction 
                                                                                                         
from (SELECT
T.c.value('@OrderNumberSequence', 'varchar(10)') ONS,
T.c.value('@Direction', 'varchar(10)') Direction,    
TP.c.value('@PDID','varchar(10)') id       
FROM   @x.nodes('/xxx/TP1') T(c)
outer apply T.c.nodes('/xxx/TP2') TP(c)     
) as X

w wyniku otrzymuję tabelkę następującej postaci np

następnie mam zapytanie Select które pobiera dane z kilku różnych tabel - połączenia za pomocą left join, która daje wynik w postaci
Tabela 1

Potrzebuję napisać złączenie dopisujące mi do tabeli 1 Kolumnę Direction z uwzględnieniem id=ID i on=ONS
Nie umiem zadeklarować zmiennej z tak aby ona pobierała id z tabeli 1

set @z = tabela1.id

Podane tabele i zapytania uprościłem, gdyż wyniki otrzymywanie są w dziesiątkach wierszy i kolumn, ale mam nadzieję że ktoś zrozumie o co mi chodzi

W opisie problemu nazywasz wynik kwerendy (zapytania) Tabelą. To wprowadza w błąd i utrudnia analizowanie kodu zarówno nam, jak i przyszłemu Tobie. Nazywaj to tak, żebyś z nazwy kwerendy wnioskował, co ona zwraca.

Wartość pola z jakiejś kwerendy do zmiennej przypisujesz np. w taki sposób

SELECT @z = tabela1.id
FROM tabela1

Ale Ty nie potrzebujesz przypisywać niczego do zmiennych. Ba! Używanie zmiennych utrudnia (a może nawet uniemożliwia) Ci wykonanie zadania.
Spróbuj pierwsze zapytanie (to z rozbijaniem XMLa) przepisać tak, by nie używać zmiennej x. Poszukaj poniżej przykładu z użyciem CROSS APPLY
https://learn.microsoft.com/en-us/sql/t-sql/xml/nodes-method-xml-data-type?view=sql-server-ver16

Następnie całość wstaw jako podzapytanie do tego drugiego zapytania, ale zamiast zmiennej z wstaw odwołanie do tabela1.id
Potem dodaj sobie warunek na x.ons = tabeja1.on i powinno być z głowy.

P.S. Nie wiem, w jakiej bazie to masz, ale w M$ xmle to ZUO :)

Panczo napisał(a):

Kopiuj

select
 z.*
,X.Direction
from
   (zapytanie Select które pobiera dane z kilku różnych tabel ) z
  left join (SELECT
T.c.value('@OrderNumberSequence', 'varchar(10)') ONS,
T.c.value('@Direction', 'varchar(10)') Direction,    
TP.c.value('@PDID','varchar(10)') id       
FROM   @x.nodes('/xxx/TP1') T(c)
outer apply T.c.nodes('/xxx/TP2') TP(c)     
)  X on  z.id=X.ID and z.on=X.ONS

Wielkie dzięki za pomoc. To złączenie wygląda bardzo obiecująco, prawdopodobnie jest to to do czego zmierzam, ale nadal nie rozumiem zadeklarowania zmiennej @x która pojawia się w linijce - ona wciąż jest zależna od z.id

FROM   @x.nodes('/xxx/TP1') T(c)

Nie bardzo rozumiem, pytasz o zmienna którą sam deklarowałeś? Bo to z twojego pierwszego posta...
Dla mnie to nie potrzebujesz w ogóle @x
Ale bez pełnych danych ciężko odpowiedzieć

Prawdopodobnie brakuje mi wiedzy aby właściwie opisać problem. Spróbuję to zrobić w takim razie w trochę inny sposób.
Mam zapytanie, którego wynikiem jest "tabela" (wiem że to nie poprawne nazewnictwo ale tak mi najłatwiej przedstawić problem )

W kolumnie 10 - mam xml

Moim zadaniem jest dołączenie kolumny 11 w której znajdzie się konkretna wartość wyciągnięta z xml. np data (1001) wygląda tak

<?xml version="1.0"?>

<DANE GŁÓWNE D1="1" D2="2" ….>

    <DANE POZIOM I DPI1 = "1x" DPI2= "" ...>
   		 <DANE POZIOM II>
     			 <SzczegółoweDane p0="0" p1="333" p2="0".../>
    		</DANE POZIOM II>
 	 </DANE POZIOM I>

	<DANE POZIOM I DPI1 = "2x" DPI2= "" ...>
   		 <DANE POZIOM II>
     			 <SzczegółoweDane p0="0" p1="999" p2="0".../>
    		</DANE POZIOM II>
 	 </DANE POZIOM I>
  
</DANE GŁÓWNE>

w kolumnie 11 ma się pojawić wartość p1 (biorąc pod uwagę że interesują mnie dane gdzie DPI1 = kolumna 1)
czyli wynik ma wyglądać

Wydaje się być proste, ale nie potrafię sobie z tym poradzić.

Przykładowe dane:

CREATE TABLE dbo.tab
	(
	id nvarchar(10) NULL,
	kol10 nvarchar(2) NULL,
	kol11 nvarchar(MAX) NULL
	)  
GO
insert into tab values ('1001','1x',N'<DANEGLOWNE D1="1" D2="2">
    <DANEPOZIOMI DPI1 = "1x" DPI2= "">
   		 <DANEPOZIOMII>
     			 <SzczegoloweDane p0="0" p1="333" p2="0"/>
    		</DANEPOZIOMII>
 	 </DANEPOZIOMI>
	<DANEPOZIOMI DPI1 = "2x" DPI2= "">
   		 <DANEPOZIOMII>
     			 <SzczegoloweDane p0="0" p1="999" p2="0"/>
    		</DANEPOZIOMII>
 	 </DANEPOZIOMI>
</DANEGLOWNE>'),
('1001','2x',N'<DANEGLOWNE D1="1" D2="2">
    <DANEPOZIOMI DPI1 = "1x" DPI2= "">
   		 <DANEPOZIOMII>
     			 <SzczegoloweDane p0="0" p1="333" p2="0"/>
    		</DANEPOZIOMII>
 	 </DANEPOZIOMI>
	<DANEPOZIOMI DPI1 = "2x" DPI2= "">
   		 <DANEPOZIOMII>
     			 <SzczegoloweDane p0="0" p1="999" p2="0"/>
    		</DANEPOZIOMII>
 	 </DANEPOZIOMI> 
</DANEGLOWNE>')
GO

Pobranie danych wg. zalożeń:

SELECT 
    t.id,
    t.kol10,
    x.value('@DPI1', 'NVARCHAR(10)') AS DPI1,
    x.value('@DPI2', 'NVARCHAR(10)') AS DPI2,
    y.value('(SzczegoloweDane/@p0)[1]', 'NVARCHAR(10)') AS p0,
    y.value('(SzczegoloweDane/@p1)[1]', 'NVARCHAR(10)') AS p1,
    y.value('(SzczegoloweDane/@p2)[1]', 'NVARCHAR(10)') AS p2
FROM dbo.tab t
CROSS APPLY (SELECT CAST(t.kol11 AS XML)) AS A(xmlData)
CROSS APPLY xmlData.nodes('/DANEGLOWNE/DANEPOZIOMI[@DPI1 = sql:column("t.kol10")]') AS B(x)
CROSS APPLY x.nodes('DANEPOZIOMII') AS C(y)

Wynik:

Musiałem wykonać jeszcze małą modyfikację, ale w końcu działa. Dziękuje za pomoc.