All the tables generated in this section, except the depts table, have a column pattern that uses the XPath ancestor notation to reference:
Leaf elements, such as <project>, under <projects>, or <salary>, under <emp>.
The element that contains the element defining the table, as <emp>, for example, contains <emp_id>>.
This notation “flattens” nested data. For more information about flattening XML data, see Chapter 7, “xmltable()”.
In this select statement, the column pattern for the dept_id column references the <dept_id>element in the <dept>, which contains the current <emp>.
declare @ dept_doc xml
select @dept_doc = doc from sample_docs where name_doc = 'depts'
select * into emps from xmltable('//emp' passing @dept_doc
columns emp_id char(4),
emp_name varchar(50),
salary money,
dept_id char(4) pattern '../../dept_id') as dept_extract
select * from emps
--------------------
emp_id emp_name salary dept_id
--------- ------------------- -------- --------
E123 Alex Allen 912.34 D123
E234 Bruce Baker 923.45 D123
E345 Carl Curtis 934.56 D123
E456 Don Davis 945.67 D234
E567 Earl Evans 956.78 D234
E678 Fred Frank 967.89 D345
E789 George Gordon 978.90 D345
E890 Hank Hartley NULL D345
E901 Isaak Idle 990.12 D345
In the phones table, the column pattern for the emp_id column references the <emp_id> element in <emp>, which contains the current element <phone>.
declare @ dept_doc xml
select @ dept_doc
= doc from sample_docs where name_doc='depts'
select * into phones
from xmltable('//phone' passing @ dept_doc
columns emp_id char(4),'../../emp_id'
phone_no varchar(50)) as dept_extract
select * from phones
---------------
emp_id phone_no
-------- ------------
E123 510.555.1987
E123 510.555.1876
E234 203.555.2333
E345 408.555.3123
E345 415.555.3987
E345 650.555.3777
E567 650.555.5001
E678 408.555.6111
E678 408.555.6222
E789 510.555.7654
E901 925.555.9991
E901 650.555.9992
E901 415.555.9993
In the projects table, the column pattern for the dept_id column references the <dept_id> element, in the <dept> that contains the current <project>.
declare @ dept_doc xml
select @ dept_doc
= doc from sample_docs where name_doc='depts'
select * into projects
from xmltable('//project' passing @ dept_doc
columns project_id char(4),
budget money,
dept_id char(4)pattern '../../dept_id')
as dept_extract
select * from projects
---------------
project_id budget dept_id
---------- ------- --------
PABC 598.76 D123
PBCD 587.65 D123
PCDE 576.54 D123
PDEF 565.43 D234
PEFG 554.32 D234
PFGH 543.21 D345
PGHI NULL D345
PHIJ 521.09 D345
declare @ dept_doc xml
select @ dept_doc
= doc from sample_docs where name_doc='depts'
select * into depts
from xmltable('//dept' passing @ dept_doc
columns dept_id char(4),
dept_name char(4)) as dept_extract
select * from depts
---------------
dept_id dept_name
-------- ------------
D123 Main
D234 Auxiliary
D345 Repair
