对于带有子查询和嵌套节点的SQL中的xml

so i have a trigger that is to make XML "transactions" of data changes to client demographic data it is working for the most part but i cannot get the node structure quite right a client can have up to 3 phone records i am trying to get each into its own phone nodes all nested under one client node instead of repeating the entire client node over again with same data running into some trouble using subquerys with phone

所以我有一个触发器,使XML“交易”的数据更改为客户端人口统计数据,它在大多数情况下工作,但我无法得到节点结构非常正确的客户端可以有多达3个电话记录我想要得到每个进入自己的手机节点都嵌套在一个客户端节点下,而不是重复整个客户端节点,相同的数据运行到使用subquerys与电话的一些麻烦

 --sample tables 
        DECLARE @phone TABLE ( MPIID int, phoneTypeNm VARCHAR(MAX), Phone VARCHAR(MAX), PhoneID int )
        DECLARE @province TABLE ( provinceID INT, provinceCd VARCHAR(MAX) )
        DECLARE @client TABLE ( FirstName VARCHAR(MAX), LastName VARCHAR(MAX), clientID INT )
        DECLARE @inserted TABLE ( provinceID int, address VARCHAR(MAX), addressid int, MPIID int )
        DECLARE @deleted TABLE ( provinceID int, address VARCHAR(MAX), addressid int, MPIID int )
        INSERT INTO @phone (MPIID, phoneTypeNm, Phone, PhoneID)
        VALUES
        (4 , 'School' , '123-4567', 123),
        (3 , 'Home' , '123-4567', 124),
        (3 , 'Work' , '765-4321', 2352),
        (1 , 'Cell' , '012-3654', 23672);
        INSERT INTO @province (provinceID, provinceCd)
        VALUES
        (1, 'ON'),
        (3, 'AB'),
        (4, 'PI'),
        (2, 'BC');
        INSERT INTO @client (FirstName, LastName, clientID)
        VALUES ('james', 'elroy', 1),
           ('andrew', 'lucy', 4),
           ('adam,', 'trevor', 3);
        INSERT INTO @inserted (provinceID, address, addressid, MPIID)
        VALUES  (2, '123 fake st', 11, 1),
            (4, '123 none st', 14, 4), 
            (1, '123 inserted st', 13, 3);
        INSERT INTO @deleted (provinceID, address, addressid, MPIID)
        VALUES (1, '123 old st', 11, 1),
           (4, '123 none st', 14, 4),
           (3, '123 deleted st', 12, 2);

code below makes xml of what data has changed this will run on a trigger to access inserted and deleted tables

下面的代码使xml的数据发生了变化,这将在触发器上运行以访问已插入和已删除的表

       DECLARE @NewTransaction XML;

    SET @NewTransaction = ( SELECT * FROM (
                 SELECT  
                 c.ClientID AS [@UniqueIdentifier]
                 ,[@Type] = CASE WHEN new.MPIID IN (SELECT new.MPIID FROM @inserted) THEN 'U' END
                 ,[FirstName] = CASE when new.MPIID = old.MPIID THEN NULL ELSE c.FirstName END
                 ,[LastName] = CASE when new.MPIID = old.MPIID THEN NULL ELSE c.LastName END
                 ,[Address/@UniqueIdentifier] = CASE when new.addressid = old.addressid THEN NULL ELSE new.addressid END
                 ,[Address/@Type] = CASE WHEN new.addressid IN (SELECT new.addressid FROM @inserted) THEN 'U' END
                 ,[Address/Street] = CASE when new.address = old.address THEN NULL ELSE new.address END
                 ,[Address/Province] = CASE when new.provinceID = old.provinceID THEN NULL ELSE p.provinceCd END

                 ,CASE when new.MPIID = old.MPIID THEN NULL ELSE ph.PhoneID END AS [Phone/@UniqueIdentifier]
                 ,[Phone/@Type] = CASE WHEN new.MPIID IN (SELECT new.MPIID FROM @inserted) THEN 'U' END
                 ,[Phone/PhoneType] = CASE when new.MPIID = old.MPIID THEN NULL ELSE ph.phoneTypeNm END
                 ,[Phone/PhoneValue] = CASE when new.MPIID = old.MPIID THEN NULL ELSE ph.Phone END

                 FROM @inserted [new] 
                 LEFT JOIN @deleted [old] ON new.addressid = old.addressid 
                 LEFT JOIN @client [c] ON new.MPIID = c.clientID 
                 LEFT JOIN @phone [ph] ON new.MPIID = ph.MPIID
                 LEFT JOIN @province [p] ON new.provinceID = p.provinceID
                 UNION
                 SELECT MPIID, 'D',
                 NULL, NULL, NULL, NULL, NULL,
                 NULL, NULL, NULL, NULL, NULL
                 FROM @deleted
                 WHERE MPIID NOT IN (SELECT MPIID FROM @inserted) 

             ) AS temp FOR XML PATH('Client'), ROOT('Root'), TYPE, ELEMENTS)

             SELECT @NewTransaction

--code produces

    <Root>
  <Client UniqueIdentifier="433979" Type="U">
    <FirstName>Herwigaaa</FirstName>
    <LastName>Farm</LastName>
    <Address Type="U" UniqueIdentifier="357653">
      <Street>asddrdsslkdjf</Street>
      <Province>Qing</Province>
    </Address>
    <Phone Type="U">
      <PhoneType>Cellular</PhoneType>
      <PhoneValue>[phone]</PhoneValue>
    </Phone>
  </Client>
  <Client UniqueIdentifier="433979" Type="U">
    <FirstName>Herwigaaa</FirstName>
    <LastName>Farm</LastName>
    <Address Type="U" UniqueIdentifier="357653">
      <Street>asddrdsslkdjf</Street>
      <Province>Qing</Province>
    </Address>
    <Phone Type="U">
      <PhoneType>Home</PhoneType>
      <PhoneValue>[phone]</PhoneValue>
    </Phone>
  </Client>
    </Root>

i am hoping to achieve xml structure below

我希望在下面实现xml结构

<Root>
  <Client UniqueIdentifier="433979" Type="U">
    <FirstName>Herwigaaa</FirstName>
    <LastName>Farm</LastName>
    <Address Type="U" UniqueIdentifier="357653">
      <Street>asddrdsslkdjf</Street>
      <Province>Qing</Province>
    </Address>
    <Phone Type="U" UniqueIdentifier="124">
      <PhoneType>Cellular</PhoneType>
      <PhoneValue>[phone]</PhoneValue>
    </Phone>
    <Phone Type="U" UniqueIdentifier="2352">
        <PhoneType>Home</PhoneType>
        <PhoneValue>[phone]</PhoneValue>
    </Phone>
  </Client>
</Root>

1 个解决方案

#1

       DECLARE @NewTransaction XML;

SET @NewTransaction = ( SELECT * FROM (
                 SELECT  
                 c.ClientID AS [@UniqueIdentifier]
                 ,[@Type] = CASE WHEN new.MPIID IN (SELECT new.MPIID FROM @inserted) AND new.MPIID IN (SELECT new.MPIID FROM @deleted) THEN 'U' END
                 ,[FirstName] = CASE when new.MPIID = old.MPIID THEN NULL ELSE c.FirstName END
                 ,[LastName] = CASE when new.MPIID = old.MPIID THEN NULL ELSE c.LastName END

                 ,[Addresses] = ( SELECT 
                  CASE when new.addressid = old.addressid THEN NULL ELSE new.addressid END AS [@UniqueIdentifier]
                 ,CASE WHEN new.addressid IN (SELECT new.addressid FROM @inserted) THEN 'U' END AS [@Type]
                 ,CASE when new.address = old.address THEN NULL ELSE new.address END AS [Street]
                 ,CASE when new.provinceID = old.provinceID THEN NULL ELSE p.provinceCd END AS [Province]
                 FROM @province p
                 WHERE new.provinceID = p.provinceID 
                 FOR XML PATH('Address'), TYPE, ELEMENTS )

                 ,[Phones] = ( SELECT 
                  CASE when new.MPIID = old.MPIID THEN NULL ELSE ph.PhoneID END AS [@UniqueIdentifier]
                 ,CASE WHEN new.MPIID IN (SELECT new.MPIID FROM @inserted) THEN 'U' END AS [@Type]
                 ,CASE when new.MPIID = old.MPIID THEN NULL ELSE ph.phoneTypeNm END AS [PhoneType]
                 ,CASE when new.MPIID = old.MPIID THEN NULL ELSE ph.Phone END AS [PhoneValue]
                 FROM @phone ph
                 WHERE new.MPIID = ph.MPIID 
                 FOR XML PATH('Phone'), TYPE, ELEMENTS ) 

                 FROM @inserted [new] 
                 LEFT JOIN @deleted [old] ON new.addressid = old.addressid 
                 LEFT JOIN @client [c] ON new.MPIID = c.clientID 
                 )AS temp
              FOR XML PATH('Client'), ROOT('Root'), TYPE, ELEMENTS)

               SELECT @NewTransaction

out puts to

out to

<Root>
  <Client UniqueIdentifier="3" Type="U">
    <FirstName>adam,</FirstName>
    <LastName>trevor</LastName>
    <Addresses>
      <Address UniqueIdentifier="13" Type="U">
        <Street>123 inserted st</Street>
        <Province>ON</Province>
      </Address>
    </Addresses>
    <Phones>
      <Phone UniqueIdentifier="124" Type="U">
        <PhoneType>Home</PhoneType>
        <PhoneValue>123-4567</PhoneValue>
      </Phone>
      <Phone UniqueIdentifier="2352" Type="U">
        <PhoneType>Work</PhoneType>
        <PhoneValue>765-4321</PhoneValue>
      </Phone>
    </Phones>
  </Client>
</Root>