Creating an XML doc with SQL Server

Question

So I have a database with 5 tables all with various data. Table 1 is called dbo.Business, table 2 is called dbo.Constraints, table 3 is called dbo.Features, table 4 is called dbo.Interfaces, and table 5 is called dbo.Modules. Using the data from these tables, I'm trying to create an XML doc with the format

<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>

I can't for the life of me figure out how to get an XML doc to come out like this (I just started learning SQL last week). Any and all help would be appreciated. Thank you for your time!

--------------- EDIT:

So with the help of you both I have successfully formatted MOST of my xml to look like what I want, but not quite. My current query is this:

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

and the only problem that I have now is that my Constraints section currently looks like this

<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>

When I need it to look like

<Constraints>
 <Security>4</Security>
 <InSecurity>400</InSecurity>
 <LoggedUsers>400</LoggedUsers>
 <Lend>2</Lend>
</Constraints>

How would I go about tackling this final problem? Thank you so much!

Are you familiar with using FOR XML PATH? Eg. SELECT * FROM sys.tables FOR XML PATH — Paul Andrew
– Paul Andrew, Commented Jun 13, 2016 at 13:34
That's what I was messing around with, but I couldn't truthfully get anything to look correct. — JDawg848
– JDawg848, Commented Jun 13, 2016 at 13:58
Can you show sample of query that returns all your data with joins, etc... can you have multiple results returned from a subquery (i.e.Modules, Features, etc...), that would affect the query format — Dave
– Dave, Commented Jun 13, 2016 at 19:31
Very important: How are the tables connected/related? is this 1:1 (as in your example) or 1:n? — Gottfried Lesigang
– Gottfried Lesigang, Commented Jun 14, 2016 at 12:30
@JDawg848 Please find an update in my answer. Please tick the acceptance check if this soves your issue, thx — Gottfried Lesigang
– Gottfried Lesigang, Commented Jun 14, 2016 at 17:02

Gottfried Lesigang · Accepted Answer · 2016-06-14 17:01:39Z

Try it like this:

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')

The result

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

UPDATE

For your additional question (which should rather be a new question actually) try this:

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')

Produces this result

<Constraints>
  <Security>4</Security>
  <InSecurity>400</InSecurity>
  <LoggedUser>400</LoggedUser>
  <Lend>2</Lend>
</Constraints>

Collectives™ on Stack Overflow

Creating an XML doc with SQL Server

1 Answer 1

UPDATE

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related