Post reply

Creating XML & XSD through SQL Query

  • May 29, 2012 at 3:28 am

    #150943

    Hi,

    I am currently been given a task of creating XML file and an XSD which will be consumed by another SSIS package. This SSIS package will use SQLXMLBulkLoad option to load data into temp tables and from there the business logic goes on..

    Could you please share any links which can help me to achieve what is required? note that the DB is Sql server 2005.

    The actual task is that i need to achieve below two tasks.

    1. Generate XML & XSD files if product is of "type1". This will be consumed by another SSIS package.

    2. Send email with the product list if the product is of "type2"

    Please suggest if i need to go for SSIS package or a Stored procedure would be enough to achieve the above two tasks.

    Thanks in advance.

    Regards,

    Suresh

    Regards,
    Suresh Arumugam

  • May 29, 2012 at 10:22 am

    #1494274

    Your XSD file should probably be crafted by hand. Some tools (SSMS included) can reverse-engineer an XSD based on an XML file however it is only a "best guess", i.e. SSMS will not implement data type constraints or proper nesting if the XML is not 100% representative of all corner cases.

    Generating XML from relational data is well-documented in Books Online and around the net.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 29, 2012 at 10:36 am

    #1494282

    If your XML will come from SQL Server tables, you can generate typed XSD by querying sys.columns for the SQL Server datatypes and translating to XML data types. Will take some work to set up a query like that, but probably worth it in the long run.

    The XML itself can be generated through the For XML query operator. Documentation on that is here: http://msdn.microsoft.com/en-us/library/ms173812.aspx

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 3 posts - 1 through 2 (of 2 total)

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