生成xml并更新表字段

 

 

 

--生成tm_xUnique对应的xml

DECLARE @x1 XML

SET @x1 = 

    CAST((SELECT a.name AS "@TableName",

  'uq_' + a.name + '_id_num' AS "UniqueConstraint/uq_name", 

  '(' + STUFF((

  SELECT ',' + name FROM sys.columns 

  WHERE a.object_id = object_id 

  AND ( name LIKE '%s_cID' ESCAPE 's' OR name LIKE '%s_cNum' ESCAPE 's' )

  FOR XML PATH('')),1,1,'')+ ')' AS "UniqueConstraint/uq_fields"

FROM sys.objects a

WHERE type = 'u' 

    AND a.name in (select tm_ctable from xt_tableml)

FOR XML PATH('Table')) AS XML)

 

UPDATE xt_tableml SET tm_xUnique = tb_xml

FROM xt_tableml

CROSS APPLY

(

     SELECT p.x.query('.') AS tb_xml 

     FROM @x1.nodes('/Table') AS p(x)

     WHERE p.x.value('(./@TableName)[1]','VARCHAR(100)') = tm_cTable

)p

 

 

--SELECT tm_cTable,tb_xml

--FROM xt_tableml

--CROSS APPLY

--(

--     SELECT p.x.query('.') AS tb_xml 

--     FROM @x1.nodes('/Table') AS p(x)

--     WHERE p.x.value('(./@TableName)[1]','VARCHAR(100)') = tm_cTable

--)p

 

 

 

 

 

 

 

 

 

 

--生成tm_xLink对应的xml

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

  DROP TABLE #tmp

 

;WITH tb(tbname , fieldname) AS 

(

SELECT b.name,a.name FROM sys.columns a

INNER JOIN sys.objects b ON a.object_id = b.object_id

WHERE type = 'u' 

AND (a.name LIKE '%s_cdept%' ESCAPE 's'  

 OR a.name LIKE '%t_cperson%' ESCAPE 't' 

 OR a.name LIKE '%s_cware%' ESCAPE 's'  

 OR a.name LIKE '%s_center%' ESCAPE 's')  

AND b.name in (select tm_ctable from xt_tableml) 

),

tb1(tbname,fieldid,fieldname) AS

(

SELECT a.tbname,

      b.fieldname,

      a.fieldname 

FROM tb a

LEFT JOIN tb b ON a.tbname = b.tbname 

   AND a.fieldname = REPLACE(b.fieldname,'id','') 

   AND PATINDEX('%ID%',b.fieldname)>0

WHERE PATINDEX('%ID%',a.fieldname) = 0

)

SELECT * INTO #tmp FROM tb1

 

DECLARE @x1 XML

SET @x1 = 

    CAST((SELECT tbname "@TableName",

           CASE WHEN CHARINDEX('deptid',fieldid) > 0 THEN fieldid END "Dept/DeptID",

           CASE WHEN CHARINDEX('dept',fieldname) > 0 THEN fieldname END "Dept/DeptName",

           CASE WHEN CHARINDEX('personid',fieldid) > 0 THEN fieldid END "Person/PersonID",

           CASE WHEN CHARINDEX('person',fieldname) > 0 THEN fieldname END "Person/PersonName",

           CASE WHEN CHARINDEX('wareid',fieldid) > 0 THEN fieldid END "Ware/WareID",

           CASE WHEN CHARINDEX('ware',fieldname) > 0 THEN fieldname END "Ware/WareName",

           CASE WHEN CHARINDEX('enter',fieldname) > 0 THEN fieldname END "Enter/EnterName"

FROM #tmp

FOR XML PATH('Table')) AS XML)

 

 

--SELECT @x1

 

 

 

UPDATE xt_tableml SET tm_xLink = tb_xml

FROM xt_tableml s

CROSS APPLY

(

     SELECT tm_cTable,

CAST(REPLACE(REPLACE(REPLACE(

(

SELECT '' + tb_xml

FROM xt_tableml

CROSS APPLY

(

SELECT CAST(p.x.query('.') AS VARCHAR(MAX)) AS tb_xml 

FROM @x1.nodes('/Table') AS p(x)

WHERE p.x.value('(./@TableName)[1]','VARCHAR(100)') = tm_cTable

)p

WHERE a.tm_cTable = tm_cTable

FOR XML PATH(''))

,'&lt;/Table&gt;&lt;Table TableName="' + tm_cTable + '"&gt;',''),'&lt;','<'),'&gt;','>') AS XML) AS tb_xml 

FROM xt_tableml a 

WHERE s.tm_cTable = tm_cTable

)p

 

--SELECT tm_cTable,

--CAST(REPLACE(REPLACE(REPLACE(

-- (

--            SELECT '' + tb_xml

--FROM xt_tableml

--CROSS APPLY

--(

--SELECT CAST(p.x.query('.') AS VARCHAR(MAX)) AS tb_xml 

--FROM @x1.nodes('/Table') AS p(x)

--WHERE p.x.value('(./@TableName)[1]','VARCHAR(100)') = tm_cTable

--)p

--WHERE a.tm_cTable = tm_cTable

--       FOR XML PATH(''))

--       ,'&lt;/Table&gt;&lt;Table TableName="' + tm_cTable + '"&gt;',''),'&lt;','<'),'&gt;','>') AS XML) 

--FROM xt_tableml a