Results 1 to 4 of 4

Thread: SQL/XML (vbnet)

  1. #1
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL/XML (vbnet)

    I am trying to combine two select statements into one and I am not geting any luck with it. Can someone help out?

    SELECT statement1
    SELECT
    XMLELEMENT( NAME "household",
    XMLATTRIBUTES( h.record_id, h.system_id, h.odbid, h.date_updated, h.date_added),
    XMLELEMENT( NAME "name",h.name),
    XMLELEMENT( NAME "hsetype",h.hsetype)
    )
    FROM ucd.dbo.tblrawhseholds h

    SELECT statement2
    SELECT
    XMLELEMENT( NAME "member",
    XMLATTRIBUTES (m.record_id, m.date_added, m.date_updated),
    XMLELEMENT( NAME "client_id",m.client_id),
    XMLELEMENT( NAME "date_entered",m.date_entered),
    XMLELEMENT( NAME "date_ended",m.date_ended),
    XMLELEMENT( NAME "head_of_household", m.head_of_household),
    XMLELEMENT( NAME "relationship", m.relationship
    )
    )
    FROM ucd.dbo.tblrawhsemembers m
    INNER JOIN ucd.dbo.tblrawhseholds h
    ON h.family_id = m.family_id

    What I am trying to get is an XML document where a household will have the xml attributes record id, date_updated etc and also will have xml elements name, type and members. the element members also have some attributes and elements that I need to include in the document. I have no idea how to tweak the statement without getting any error. please help.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL/XML (vbnet)

    What kind of data entities are tblrawhseholds and tblrawhsemembers? You say you are trying to get an XML document, but are you trying to export data into an xml format or are you trying to read data from xml documents? From your SQL, it looks like you might be working with SQL Server tables, and if so, you need ot indicate version. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Charlotte

  3. #3
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL/XML (vbnet)

    I am trying to export data into an xml document following a predefined schema. I' using sql2000.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL/XML (vbnet)

    Where does the schema live? Normally, an xml schema is in a schema file, sor there is no point in trying to read it with SQL, which doesn't work anyhow. Do I understand that you want to read data from the two tables and export that data to xml using the schema you have been given? What kind of document is the schema in? You need to create an XMLDataWriter to write the data to an xml file. Do you have any experience reading and writing xml?
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •