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