Post reply

XSD Schema

  • June 3, 2004 at 9:22 pm

    #108325

    Can someone tell me how do I create an XSD Schema for some or all of the tables in a Database? I have successfully extracted the data in XML Format using the FOR XML Clause in my SELECT Statement but how do I just extract the schema to use as a template?

    Kindest Regards,

  • June 4, 2004 at 2:57 am

    #508973

     

    Will XMLDATA in for xml help?

  • June 4, 2004 at 5:37 am

    #508991

    I still have to use a Mode in the FOR XML Clause. I can't just put in XMLDATA. Is there a way of doing this?

    Kindest Regards,

  • June 4, 2004 at 12:39 pm

    #509079

    IF YOU HAVE VISUAL STUDIO 2003, THEN IT CAN GENERATE AN XSD SCHEMA BASED ON THE XML DATA FILE/DOC.

  • June 4, 2004 at 2:35 pm

    #509090

    try 

    SELECT ...........

    for XML AUTO, XMLDATA

     

    from

    Tony

  • June 4, 2004 at 2:58 pm

    #509094

    You could use something like this:

    --Use to lookup all columns in Table

    select  sc.name, st.Name as DataType, sc.length,

     Case isnullable

      When 1 then 'YES'

      Else 'NO'

     END as isnullable

    from  sysobjects so (nolock)

    JOIN syscolumns sc (nolock) on so.id = sc.id

    JOIN systypes st (nolock) on st.xtype = sc.xtype

    order  by sc.name for XML Auto

     

    Should probably use INFORMATION_SCHEMA views instead

    Signature is NULL

  • June 4, 2004 at 7:15 pm

    #509117

    Where are all the XMLGURUS? Are there any out there?

    Thanks for the replys but none are getting me anywhere!

    I'm using the SQLXMLBulkLoad process to insert data to a couple of tables in a Database. This Utility requires 2 files. 1 is the XSD Schema file and the other is the data in XML format.

    Performing a FOR XML AUTO, XMLDATA at the end of my query does not create the XSD Schema that is required for the SQLXMLBulkLoad. In addition, what is a proper way of retrieving data in XML format as doing it in query analyzer truncates data as well as omits certain rows from the recordset. YES! I have enabled DBCC TRACEON(257) to TRY and get a readable XML Resultset.

    I hope anyone can assist me as I'm about to toss XML in the bin! Surley you can create a XSD Schema for the SQLXMLBulkLoad without having to write it from scratch, that would take forever.

    yaweah,

    are you able to further explain if this can be achieved in Visual Studio.Net 2003?

    Kindest Regards,

  • June 5, 2004 at 7:05 pm

    #509138

    If you want to get the xml through the query analyzer and the xml message is longer than 256 characters long, you need to go to "Tools|Options" in query analyzer and go to the "Results" tab and increase the field "Maximuh characters per column". Default is 256, max is 8192.

    Then run you query, this is an example from Northwind:

    Select Top 2 * From Customers

    Where Country = 'USA'

    For Xml Auto

    Copy the xml and paste it into Notepad. Make sure you turn off Word Wrap, the XSD schema converter doesn't like newline characters. The xml SQL server generates when you use the Auto keyword isn't well formed, you need a root element to be able to convert it to an XSD. I added a starting tag called "<Body>" that I insert before the tag "<Customers..." and I put an ending tag called "</Body>" at the end of the message. Don't put the double quotes in the message.

    Save the file with the extension xml, I called it "Customers.xml".

    Then run the command line tool called "xsd.exe" which you can find where you installed Visual Studio.net and the "bin" folder. Enter this from the command line:

    xsd C:\Customers.xml /out:C:\

    Presto! You should now have an XSD schema called Customers.XSD at C:\ if you named you xml file Customers.xml.

    If you want to generate an XSD schema for each table, then use Calvin's script or similar.

    I never use query analyzer to generate an xml document, I prefer to call the query/sproc from a .Net application, save it through the XmlDocument object or DataSet.WriteXml method and either use Xsd.exe or XML Spy (if you have that).

    HTH,

    Magnus

     

     

     

  • June 6, 2004 at 2:38 am

    #509144

    maqs,

    You have done well! I have just gone through what you siad and well....I'm nearly there. Th epoint of all this is to be able to use the SQLXMLBulkLoad to insert data into a couple of tables. The SQLXMLBulkLoad takes 2 parameters. 1 is the XSD Schema and the other is the Data in XML format.

    I know I have the data correct, I just need to get the schema. The schema that the xsd utility you have shown me generates the schema I need howvere, the SQLXMLBulLoad does load the data into the table. The VBScript completes but the data has not inserted into the table.

    Kindest Regards,

  • June 6, 2004 at 6:30 am

    #509150

    I'm glad to hear you got that piece to work! Unfortunately I've never used SQLXmlBulkLoad so I'm not sure what else might be wrong.

  • June 6, 2004 at 6:54 pm

    #509167

    Maqs,

    Thanks for all that info. I modified  acouple of tags in the XSD schema and the SQLXMLBulkLoad works. That xsd utility is awsome!

    Talk to you later.

    Kindest Regards,

Viewing 11 posts - 1 through 10 (of 10 total)

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