Post reply

Generate XML from SQL

  • July 24, 2013 at 3:02 am

    #296002

    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?

  • July 24, 2013 at 3:41 am

    #1635260

    SELECT Field1 AS [@AttributeNameHere]

    N 56°04'39.16"
    E 12°55'05.25"

  • July 24, 2013 at 4:29 am

    #1635280

    SELECT Field1 AS [@AttributeNameHere]

    Can you elaborate on this?

  • July 24, 2013 at 4:54 am

    #1635288

    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"

  • July 24, 2013 at 5:07 am

    #1635295

    I've seen your answer but can you explain your answer please?

  • July 24, 2013 at 5:09 am

    #1635296

    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"

  • July 24, 2013 at 5:40 am

    #1635304

    Thanks SwePeso - please could you give an example as it doesn't seem to be working for me.

  • July 24, 2013 at 5:51 am

    #1635309

    DECLARE@Sample TABLE

    (

    Col1 VARCHAR(20) NOT NULL,

    Col2 INT NOT NULL

    );

    INSERT@Sample

    (

    Col1,

    Col2

    )

    VALUES('SwePeso', 1),

    ('Demo', 2);

    SELECTCol2 AS [Element/@Age],

    Col1 AS [Element]

    FROM@Sample

    FOR XMLPATH(''),

    ROOT('Data');

    N 56°04'39.16"
    E 12°55'05.25"

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply