Detailed description of SQL joint query and XML parsing instances, sqlxml

Detailed description of SQL joint query and XML parsing instances, sqlxml

SQL Union query and XML parsing instances

 Here is an example of how to implement this function:

(Select. EBILLNO,. EMPNAME,. APPLYDATE, B. HS_NAME, replace (. SUMMARY, char (10), ''), char (13),'') as SUMMARY, cast (c. xmlData as XML ). value ('(/List/item/No/text () [1]', 'nvarchar (300) ') as No, cast (c. xmlData as XML ). value ('(/List/item/zje/text () [1]', 'nvarchar (300) ') as zje, cast (c. xmlData as XML ). value ('(/List/item/yfje/text () [1] ', 'nvarchar (300)') as yfje, cast (c. XMLData as XML ). value ('(/List/item /Bcje/text () [1] ', 'nvarchar (300)') as bcje, cast (c. XMLData as XML ). value ('(/List/item/URL/text () [1]', 'nvarchar (300) ') as URL, cast (c. XMLData as XML ). value ('(/List/item/Remark/text () [1]', 'nvarchar (300) ') as BZ, cast (p. XMLData as XML ). value ('(/NewDataSet/Table1/UserName/text () [1]', 'nvarchar (500) ') as SKRXM, ('HTTP ://......? Sid = 3 & mid = 7281 & PID = '+. PID) as bxdljdzfrom Ex_Bill as a left join Ex_System_Cfg as B on (. BILLSYSTEMID = B. HS_ID and. DATASYSTEMID = B. SYSTEM_NAME) left join (select * from [10.2.3.39]. aspireworkFlow. dbo. repeaingTable) as c on (c. keyword = 'url' and c. processID =. PID) left join (select * from [10.2.3.39]. aspireworkFlow. dbo. repeaingTable) as d on (d. keyword = 'fkxx _ new' and d. processID =. PID or d. keyword = 'fk Xx' and d. processID =. PID) left join (select * from EX_BillExtension) as p on. BILLNO = p. BILL_NOwhere applyempid = 'zhongxun 'and. EBILLNO is not nulland status> 5 and status not in (200,100,700 0) and. APPLYDATE> '2017-01-01 'and. HT = 'is' and cast (d. XMLData as XML ). value ('(/List/item/SKRXM/text () [1]', 'nvarchar (300) ') is null) union (select e. EBILLNO, e. EMPNAME, e. APPLYDATE, f. HS_NAME, replace (e. SUMMA RY, char (10), ''), char (13),'') as SUMMARY, cast (g. xmlData as XML ). value ('(/List/item/No/text () [1]', 'nvarchar (300) ') as No, cast (g. xmlData as XML ). value ('(/List/item/zje/text () [1]', 'nvarchar (300) ') as zje, cast (g. xmlData as XML ). value ('(/List/item/yfje/text () [1] ', 'nvarchar (300)') as yfje, cast (g. XMLData as XML ). value ('(/List/item/bcje/text () [1]', 'nvarchar (300) ') as bcje, cast (g. XMLData as XML ). value ((/List/item/URL/text () [1] ', 'nvarchar (300)') as URL, cast (g. XMLData as XML ). value ('(/List/item/Remark/text () [1]', 'nvarchar (300) ') as BZ, cast (h. XMLData as XML ). value ('(/List/item/SKRXM/text () [1]', 'nvarchar (300) ') as SKRXM, ('HTTP ://......? Sid = 3 & mid = 7281 & PID = '+ e. PID) as bxdljdzfrom Ex_Bill as e left join Ex_System_Cfg as f on (e. BILLSYSTEMID = f. HS_ID and e. DATASYSTEMID = f. SYSTEM_NAME) left join (select * from [10.2.3.39]. aspireworkFlow. dbo. repeaingTable) as g on (g. keyword = 'url' and g. processID = e. PID) left join (select * from [10.2.3.39]. aspireworkFlow. dbo. repeaingTable) as h on (h. keyword = 'fkxx _ new' and h. processID = e. PID or h. keyword = 'fkxx' and h. processID = e. PID) where applyempid = 'zhongxun 'and e. EBILLNO is not nulland status> 5 and status not in (200,100,700 0) and e. APPLYDATE> '2017-01-01 'and e. HT = 'is' and cast (h. XMLData as XML ). value ('(/List/item/SKRXM/text () [1]', 'nvarchar (300) ') is not null)

When writing SQL statements, the difficulty lies not in SQL itself, but in logic. After writing this SQL statement, the logic is not that difficult.

The Union clause is used to put the tables queried by both groups into one.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!