XML Output via SQL Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

XML Output via SQL Server

Author

Topic

system243trd
Starting Member

9 Posts

Posted - 2013-07-23 : 16:11:11

I am fairly new to XML and have been doing some research.

I need to be able to create the following XML output from a couple of SQL tables

Table 1
Fielda (primary key)
Fieldb
Fieldc

Table
FieldD (foreign key)
fieldE

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" version="1.0">
<xs:element name="examplea " >
<xs:element name="schemaVersion" type="xs:decimal" use="required" fixed="1.0" />
<xs:complexType >
<xs:sequence>
<xs:element name="confirmationDetail" minoccurs="1" maxoccurs="3000">
<xs:complexType>
<xs:sequence >
<xs:element name="fielda" type="string" maxlength="50"/>
<xs:element name="fieldb" type="string" maxlength="50"/>
<xs:element name="fieldc" type="date"/>
<xs:element name="fieldd" type="integer" maxlength="10"/>
<xs:element name="fielde" type="string" maxlength="20"/>
<xs:restriction>
<xs:enumeration value="A"/>
<xs:enumeration value="B"/>
<xs:enumeration value="C"/>
</xs:restriction>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Is it possible to create this output in SQL Server?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-23 : 16:17:16

Yes.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-23 : 16:17:38

[code]SELECT
fielda,
fieldb,
fieldc,
fieldd,
fielde
FROM
Table1 t1
INNER JOIN Table2 t2 ON
t1.fielda = t2.fieldd
FOR XML PATH('confirmationDetail'),ROOT('examplea');[/code]

system243trd
Starting Member

9 Posts

Posted - 2013-07-23 : 16:29:39

Thanks James,

I have tried that but the element name isn't outputting the type after the element name.

The xs:enumeration part isn't being returned either.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-23 : 16:36:03

SELECT SomeColumnHere AS [@AttributeNameHere]...

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

system243trd
Starting Member

9 Posts

Posted - 2013-07-23 : 16:39:12

Thanks SweetPotato :s

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-24 : 07:52:04

[code]DECLARE @Sample TABLE
(
Col1 VARCHAR(20) NOT NULL,
Col2 INT NOT NULL
);

INSERT @Sample
(
Col1,
Col2
)
VALUES ('SwePeso', 1),
('Demo', 2);

SELECT Col2 AS [Element/@Age],
Col1 AS [Element]
FROM @Sample
FOR XML PATH(''),
ROOT('Data');[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources