Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generate XML from SQL Expand / Collapse
Author
Message
Posted Wednesday, July 24, 2013 3:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 4, 2014 3:14 PM
Points: 14, Visits: 82
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?
Post #1476932
Posted Wednesday, July 24, 2013 3:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 2,397, Visits: 3,417
SELECT Field1 AS [@AttributeNameHere]


N 56°04'39.16"
E 12°55'05.25"
Post #1476945
Posted Wednesday, July 24, 2013 4:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 4, 2014 3:14 PM
Points: 14, Visits: 82
SELECT Field1 AS [@AttributeNameHere]

Can you elaborate on this?
Post #1476968
Posted Wednesday, July 24, 2013 4:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 2,397, Visits: 3,417
See your post here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=187048
and my answer.



N 56°04'39.16"
E 12°55'05.25"
Post #1476980
Posted Wednesday, July 24, 2013 5:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 4, 2014 3:14 PM
Points: 14, Visits: 82
I've seen your answer but can you explain your answer please?
Post #1476990
Posted Wednesday, July 24, 2013 5:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 2,397, Visits: 3,417
If you prefix your column name with "@", the value becomes an attribute instead of an element.


N 56°04'39.16"
E 12°55'05.25"
Post #1476991
Posted Wednesday, July 24, 2013 5:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 4, 2014 3:14 PM
Points: 14, Visits: 82
Thanks SwePeso - please could you give an example as it doesn't seem to be working for me.

Post #1477001
Posted Wednesday, July 24, 2013 5:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 2,397, Visits: 3,417
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');




N 56°04'39.16"
E 12°55'05.25"
Post #1477009
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse