SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 XML Output via SQL Server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

system243trd
Starting Member

9 Posts

Posted - 07/23/2013 :  16:11:11  Show Profile  Reply with Quote
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

Sweden
30242 Posts

Posted - 07/23/2013 :  16:17:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3657 Posts

Posted - 07/23/2013 :  16:17:38  Show Profile  Reply with Quote
SELECT
	fielda,
	fieldb,
	fieldc,
	fieldd,
	fielde
FROM
	Table1 t1
	INNER JOIN Table2 t2 ON 
		t1.fielda = t2.fieldd
FOR XML PATH('confirmationDetail'),ROOT('examplea');
Go to Top of Page

system243trd
Starting Member

9 Posts

Posted - 07/23/2013 :  16:29:39  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 07/23/2013 :  16:36:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT SomeColumnHere AS [@AttributeNameHere]...



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

system243trd
Starting Member

9 Posts

Posted - 07/23/2013 :  16:39:12  Show Profile  Reply with Quote
Thanks SweetPotato :s
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 07/24/2013 :  07:52:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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');



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000