所以我有一个包含5个表的数据库,所有表都有不同的数据。表1叫做dbo.Business,表2叫做dbo.Constraints,表3叫做dbo.Features,表4叫做dbo.Interfaces,表5叫做dbo.Modules。使用这些表中的数据,我试图创建一个XML文档

<Lin key="####">     <----- comes from table 1
  <G ID="####">  <----- comes from table 1
    <Constraints>
        <Security>2</Security>   <----- more data like this, all comes from table 2
    </Constraints>
    <Modules>
        <Module id="1" name="DV" registered="false"/>   <----- more data like this, all comes from table 5
    </Modules>
    <Features>
        <Feature id="0" name="DEC" registered="true" expiration=""/>    <----- more data like this, all comes from table 3
    </Features>
    <Interfaces>
        <Interface id="1" name="SI" registered="false" expiration="" rulename="HIS"/>  <----- more data like this, all comes from table 4
    </Interfaces>
  </G>
</Lin>

我一辈子都搞不懂如何让XML文档像这样出来(我上周才开始学习SQL)。任何帮助都将不胜感激。谢谢你抽出时间!
---------------编辑:
所以在你们的帮助下,我已经成功地格式化了我的大部分xml,使之看起来像我想要的,但不是很像。我现在的问题是:
SELECT OrgID as SomeID, Licensingkey as SomeKey INTO Business FROM TestDB.dbo.Business

SELECT ProductName as SecurityConstraint, Amount as Number INTO Constraints FROM TestDB.dbo.Constraints

SELECT FeatureID as id, FeatureName as name, Included as registered, Expiration as expiration INTO Features FROM TestDB.dbo.Features

SELECT ModuleID as id, ProductName as name, Included as registered INTO Modules FROM TestDB.dbo.Modules

SELECT InterfaceID as id, InterfaceName as name, Included as registered, Expiration as expiration, RuleName as rules INTO Interfaces FROM TestDB.dbo.Interfaces

SELECT SomeKey AS [@key]
      ,SomeID AS [G/@OrgID]

  ,(SELECT Constraints.Number
    FROM Constraints
    FOR XML PATH(''),TYPE
   ) AS [G/Constraints]

   ,(SELECT Modules.registered AS [@registered]
          ,Modules.name AS [@name]
          ,Modules.id AS [@id]
    FROM Modules
    FOR XML PATH('Module'),TYPE
   ) AS [G/Modules]

  ,(SELECT Features.registered AS [@registered]
          ,Features.name AS [@name]
          ,Features.id AS [@id]
          ,IsNull(cast(Features.expiration as varchar(10)), '' ) AS [@expiration]
    FROM Features
    FOR XML PATH('Feature'),TYPE
   ) AS [G/Features]

   ,(SELECT Interfaces.registered AS [@registered]
          ,Interfaces.name AS [@name]
          ,Interfaces.id AS [@id]
          ,IsNull(cast(Interfaces.expiration as varchar(10)), '' ) AS [@expiration]
          ,Interfaces.rules as [@rulename]
    FROM Interfaces
    FOR XML PATH('Interface'),TYPE
   ) AS [G/Interfaces]

FROM Business
FOR XML PATH('Lin')
DROP TABLE master.dbo.Business, master.dbo.Constraints, master.dbo.Features, master.dbo.Modules, master.dbo.Interfaces
GO

我现在唯一的问题是我的约束部分现在看起来像这样
<Constraints>
  <Number>4</Number>
  <SecurityConstraint>Security</SecurityConstraint>
  <Number>400</Number>
  <SecurityConstraint>InSecurity</SecurityConstraint>
  <Number>400</Number>
  <SecurityConstraint>LoggedUsers</SecurityConstraint>
  <Number>2</Number>
  <SecurityConstraint>Lend</SecurityConstraint>
</Constraints>

当我需要它看起来像
<Constraints>
 <Security>4</Security>
 <InSecurity>400</InSecurity>
 <LoggedUsers>400</LoggedUsers>
 <Lend>2</Lend>
</Constraints>

我该如何着手解决最后一个问题?非常感谢!

最佳答案

试试这个:

DECLARE @t1 TABLE(SomeKey VARCHAR(10), SomeID INT);
INSERT INTO @t1 VALUES('Key1',1);

DECLARE @t2 TABLE(SecurityConstraint INT);
INSERT INTO @t2 VALUES(2);

DECLARE @t3 TABLE(id INT,name VARCHAR(100),registered BIT);
INSERT INTO @t3 VALUES(1,'DV',0);

SELECT SomeKey AS [@key]
      ,SomeID AS [G/@ID]

      ,(SELECT t2.SecurityConstraint AS [Security]
        FROM @t2 AS t2
        FOR XML PATH(''),TYPE
       ) AS [G/Constraints]

      ,(SELECT t3.id AS [@id]
              ,t3.name AS [@name]
              ,t3.registered AS [@registered]
        FROM @t3 AS t3
        FOR XML PATH('Module'),TYPE
       ) AS [G/Modules]

       --Other tables similar...
FROM @t1 AS t1
FOR XML PATH('Lin')

结果
<Lin key="Key1">
  <G ID="1">
    <Constraints>
      <Security>2</Security>
    </Constraints>
    <Modules>
      <Module id="1" name="DV" registered="0" />
    </Modules>
  </G>
</Lin>

更新
对于您的附加问题(实际上应该是一个新问题),请尝试以下操作:
DECLARE @tbl TABLE(SecurityConstraint VARCHAR(100),Amount INT);
INSERT INTO @tbl VALUES
 ('Security',4)
,('InSecurity',400)
,('LoggedUser',400)
,('Lend',2);

SELECT CASE WHEN SecurityConstraint='Security' THEN Amount ELSE NULL END AS [Security]
      ,CASE WHEN SecurityConstraint='InSecurity' THEN Amount ELSE NULL END AS [InSecurity]
      ,CASE WHEN SecurityConstraint='LoggedUser' THEN Amount ELSE NULL END AS LoggedUser
      ,CASE WHEN SecurityConstraint='Lend' THEN Amount ELSE NULL END AS Lend
FROM @tbl
FOR XML PATH(''),ROOT('Constraints')

产生这个结果
<Constraints>
  <Security>4</Security>
  <InSecurity>400</InSecurity>
  <LoggedUser>400</LoggedUser>
  <Lend>2</Lend>
</Constraints>

关于mysql - 使用SQL Server创建XML文档,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/37791078/

10-11 22:43
查看更多