New to XML

I need to exoprt to an outside service provider data from our database in XML format.

They have coded a pod in flash and I want them to execute a stored procedure on our SQL Server that will create two xml data files that they will use to populte 2 table on their MYSql database.  Is this a possibility, and if so do you have any samples I could see?  I have attached the queries that will be in the SP.
--
-- Select data for Webcast Table
--
select Product_code,description,begin_date,start_time,end_time,cle_credits,ethics_credits 
from webv_program_catalog
where product_code = @WEBCAST
 
--
-- Select data for the Presenters(Volunteers) Table
--
select product_code,full_name, last_first
from web_volunteers
where product_code = @WEBCAST

Open in new window

Jeff_KingstonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QuintainTCommented:
If you add "For XML AUTO" to the end of your Select queries then the result is output as XML. There are a number of options you can use with "For XML". Is that the sort of thing you are after?
0
Jeff_KingstonAuthor Commented:
I want to generate the xml so the outside vendor can us it to populate some tables on their site.  The AUTO option seems to generate a huge string such as
<webcast Product_code="2090240P01" description="Medicaid Planning Update 2009" begin_date="May 19, 2009" start_time="08:30:00" end_time="12:30:00" cle_credits="4.00"/>
and
<volunteers product_code="2090240P01" full_name="Todd E. Lutsky, Esq." last_first="LUTSKY, TODD"/><volunteers product_code="2090240P01" full_name="Rebecca J. Benson, Esq." last_first="BENSON, REBECCA"/><volunteers product_code="2090240P01" full_name="Vince
etc
I'm not very conversant with xml.  Can these files be used to populate tables on their site?
0
QuintainTCommented:
That is valid XML but it has produced all the row data as attributes. If you want to have the row data as  it as elements you'll have to add some kewords to the command. I suggest you find out the XML structure that your outside service provider prefers and then we can look at how to produce it.

e.g. you are producing the data as attributes like so
<webcast Product_code="2090240P01" description="Medicaid Planning Update 2009" begin_date="May 19, 2009" start_time="08:30:00" end_time="12:30:00" cle_credits="4.00"/>

As child elements it would look like this
<webcast>
  <Product_code>2090240P01</Product_code>
  <description>Medicaid Planning Update 2009</description>  
  <begin_date>May 19, 2009</begin_date>
  <start_time>08:30:00</start_time>
  <end_time>12:30:00</end_time>
  <cle_credits>4.00</cle_credits>
</webcast>
0
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Jeff_KingstonAuthor Commented:
when the table hase multiple entries how do we get the <row> tag inserted around each occurence?
0
QuintainTCommented:
I'll check that out for you and get back. But you really need to know if your outside service supplier has a requirement for a particular layout, or if we have a free hand.
0
Jeff_KingstonAuthor Commented:
The two examples below are what they prefer, but they want to put a wrapper around the two output
xml formats and make it one xml file (A wrapper?)


<webcast>
  <Product_code>2090240P01</Product_code>
  <description>Medicaid Planning Update 2009</description>  
  <begin_date>May 19, 2009</begin_date>
  <start_time>08:30:00</start_time>
  <end_time>12:30:00</end_time>
  <cle_credits>4.00</cle_credits>
</webcast>
 
<Presenters>
<row>
  <Product_code>2090240P01</Product_code>
  <Full_Name>Todd E. Lutsky, Esq.</Full_Name >
  <Last_First>LUTSKY, TODD</Last_First>
</row>
<row>
  <Product_code>2090240P01</Product_code>
  <Full_Name>Rebecca J. Benson, Esq.</Full_Name >
  <Last_First>BENSON, REBECCA</Last_First>
</row>
<row>
  <Product_code>2090240P01</Product_code>
  <Full_Name>Vincent E. Bonazzoli, Esq.</Full_Name >
  <Last_First>BONAZZOLI, VINCENT</Last_First>
</row>
<row>
  <Product_code>2090240P01</Product_code>
  <Full_Name>Leo J. Cushing, Esq.</Full_Name >
  <Last_First>CUSHING, LEO</Last_First>
</row>
</Presenters>
 
0
QuintainTCommented:
try this,
for xml auto,elements

Let me know if this is close...
0
Jeff_KingstonAuthor Commented:
Still doesn't insert the <row> ..... </row> around the multiple returns for the presenter table
0
QuintainTCommented:
\which version of SQL Server are you using.?
0
Jeff_KingstonAuthor Commented:
sql server 2000
0
Jeff_KingstonAuthor Commented:
I found a product that will retrieve data as xml and this is a sample output

<?xml version="1.0" encoding="UTF-8" ?>
- <table name="query">
- <row>
  <value column="product_code">product_code</value>
  <value column="full_name">full_name</value>
  <value column="last_first">last_first</value>
  </row>
- <row>
  <value column="product_code">2090240P01</value>
  <value column="full_name">Todd E. Lutsky, Esq.</value>
  <value column="last_first">LUTSKY, TODD</value>
  </row>
- <row>
  <value column="product_code">2090240P01</value>
  <value column="full_name">Rebecca J. Benson, Esq.</value>
  <value column="last_first">BENSON, REBECCA</value>
  </row>
- <row>
  <value column="product_code">2090240P01</value>
  <value column="full_name">Vincent E. Bonazzoli, Esq.</value>
  <value column="last_first">BONAZZOLI, VINCENT</value>
  </row>
- <row>
  <value column="product_code">2090240P01</value>
  <value column="full_name">Leo J. Cushing, Esq.</value>
  <value column="last_first">CUSHING, LEO</value>
  </row>
  </table>
0
QuintainTCommented:
Ok, that will make things slightly slower. I'll need to do some investigation and get back later (maybe tomorrow...).

I assume this is going to be a regular requirement rather than a one off? How automatic does it need to be?
0
QuintainTCommented:
It should be feasible to do this in SQL Server. It's just understanding the end result that is required.
I think you want the following. Can you confirm...?

<root>
  <webcast>
    <Product_code>2090240P01</Product_code>
    <description>Medicaid Planning Update 2009</description>  
    <begin_date>May 19, 2009</begin_date>
    <start_time>08:30:00</start_time>
    <end_time>12:30:00</end_time>
    <cle_credits>4.00</cle_credits>
  </webcast>
  <webcast>
    ....
  </webcast>
  <Presenters>
    <row>
      <Product_code>2090240P01</Product_code>
      <Full_Name>Todd E. Lutsky, Esq.</Full_Name >
      <Last_First>LUTSKY, TODD</Last_First>
    </row>
    <row>
      ....
    </row>
</Presenters>
0
Jeff_KingstonAuthor Commented:
There may be at most 12 records for one webcast, and I'd like the code to be a stored procedure.
0
QuintainTCommented:
Ooops should be an end tag like this

</Presenters>
</root>
0
Jeff_KingstonAuthor Commented:
Absolutely correct
0
Anthony PerkinsCommented:
This is how you do it:

Select      1 Tag,
            Null Parent,
            Null [Presenters!1!Row!Element],
            Null [Row!2!Product_Code!Element],
            Null [Row!2!Full_Name!Element],
            Null [Row!2!Last_Name!Element]

UNION ALL

Select      2 Tag,
            1,
            Null,
            Product_Code,
            Full_Name,
            Last_Name
From      web_volunteers
Where      product_code = @WEBCAST
For XML Explicit
0
QuintainTCommented:
A good call from acperkins. However I've added the bits to include the other table. This should give you the structure I think you want.

create procedure MySP
 @WEBCAST nvarchar(30)
as
begin
Select      1 Tag,
            Null Parent,
            Null [Root!1!webcast!Element],
            Null [webcast!2!Product_Code!Element],
            Null [webcast!2!description!Element],
            Null [webcast!2!begin_date!Element],
            Null [webcast!2!start_time!Element],
            Null [webcast!2!end_time!Element],
            Null [webcast!2!cle_credits!Element],
            Null [Root!1!row!Element],
            Null [Row!3!Product_Code!Element],
            Null [Row!3!Full_Name!Element],
            Null [Row!3!Last_First!Element]
UNION ALL

Select      2 Tag,
            1,
            Null,
            Product_Code,
            description,
            begin_date,
            start_time,
            end_time,
            cle_credits,
            null,
            null,
            null,
            null

From      webv_program_catalog
Where      product_code = @WEBCAST
UNION ALL

Select      3 Tag,
            1,
            Null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
            Product_Code,
            Full_Name,
            Last_First

From      web_volunteers
Where      product_code = @WEBCAST
For XML Explicit
end
0
Jeff_KingstonAuthor Commented:
I'll give this a try today
0
Jeff_KingstonAuthor Commented:
Doesn't format the data correctly
0
QuintainTCommented:
Could you provide a little more information so I can trace what is incorrect? What result are you getting/expecting. My test data returns the following for a cakk of MySP '11111A'

<Root>
  <webcast>
    <Product_Code>11111A                        </Product_Code>
    <description>product A                                         </description>
    <begin_date>2009-04-01T00:00:00</begin_date>
    <start_time>2009-04-01T10:00:00</start_time>
    <end_time>2009-04-01T12:00:00</end_time>
    <cle_credits>5.50</cle_credits>
  </webcast>
  <Row>
    <Product_Code>11111A                        </Product_Code>
    <Full_Name>John Smith                                        </Full_Name>
    <Last_First>Smityh,John                                       </Last_First>
  </Row>
</Root>
0
Jeff_KingstonAuthor Commented:
<Root><webcast>
<Product_Code>2090566WBC</Product_Code>
<description>The Court Jester: Why Ineffective Assistance of Counsel Is No Laughing Matter</description>
<begin_date>Jun 08, 2009</begin_date><start_time>16:00:00</start_time><end_time>17:00:00</end_time

!!!!!!!!!!!!!!!!!!!!!!!!!!!   This is all that the code above outputs  !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!1
0
Anthony PerkinsCommented:
I am afraid that the Xml you have posted is not complete so you have either butchered the query or simply copy and pasted a portion of the Xml.  I suggest you post your query and we can point out what you are missing.
0
QuintainTCommented:
Gents,
I can see that I have missed outputting the <Presenters> tag in my SQL. I'll check that out ASAP, but I'm away from my development machine for a bit. (Feel free to correct this if you have time acperkins)

Jeff,
I think the reason you are only seeing part of the query result is the way SQL2000 displays it in the query window. As I recall it chops the result (Sorry I'm on SQL2005 which gives you a link to display the result). However when called normally it will return the full set of data. I'll see if I can come up with a way to demonstrate this...
0
Jeff_KingstonAuthor Commented:
acperkins......
  As far as butchering the query, I cut and pasted QuintainT's code from above.  This exchange is to solve problems, not provide nasty terse comments
0
Anthony PerkinsCommented:
>>Feel free to correct this if you have time acperkins<<
There is nothing to correct

>>As I recall it chops the result<<
That is exactly right.  The author failed to paste the entire output from SQL Query Analyzer, because by default it only displays 256 bytes.  If they had taken the trouble to change the options to the max 8192 bytes they would have seen a tad more.
0
Anthony PerkinsCommented:
>>This exchange is to solve problems, not provide nasty terse comments<<
Please do us a favor and get off your high horse.  We are volunteers here helping you, remember?
0
Jeff_KingstonAuthor Commented:
QuintainT

The corrected out put is as follows, buth there is still a MINOR problem.

Results are:
<Root>
<webcast>
      <Product_Code>2090566WBC</Product_Code>
      <description>The Court Jester: Why Ineffective Assistance of Counsel Is No Laughing Matter</description>
      <begin_date>Jun 08, 2009</begin_date>
      <start_time>16:00:00</start_time>
      <end_time>17:00:00</end_time>
      <cle_credits>1.00</cle_credits>
</webcast>

      <Row>
            <Product_Code>2090566WBC</Product_Code>
            <Full_Name>John W. Marshall, Esq.</Full_Name>
            <Last_First>MARSHALL, JOHN</Last_First>
      </Row>
      <Row>
            <Product_Code>2090566WBC</Product_Code>
            <Full_Name>Claudia Leis Bolgen, Esq.</Full_Name>
            <Last_First>BOLGEN, CLAUDIA</Last_First>
      </Row>
      <Row><Product_Code>2090566WBC</Product_Code>
            <Full_Name>David A.F. Lewis, Esq.</Full_Name>
            <Last_First>LEWIS, DAVID</Last_First>
      </Row>

</Root>


The resuts need the <Presenters> tag, as I have manually added below.




<Root>
      <webcast>
            <Product_Code>2090566WBC</Product_Code>
            <description>The Court Jester: Why Ineffective Assistance of Counsel Is No Laughing Matter</description>
            <begin_date>Jun 08, 2009</begin_date>
            <start_time>16:00:00</start_time>
            <end_time>17:00:00</end_time>
            <cle_credits>1.00</cle_credits>
            <ethics_credits>1.00</ethics_credits>
      </webcast>
                      <presenters>
        <Row>
            <Product_Code>2090566WBC</Product_Code>
            <Full_Name>John W. Marshall, Esq.</Full_Name>
            <Last_First>MARSHALL, JOHN</Last_First>
        </Row>
        <Row>
            <Product_Code>2090566WBC</Product_Code>
            <Full_Name>Claudia Leis Bolgen, Esq.</Full_Name>
            <Last_First>BOLGEN, CLAUDIA</Last_First>
        </Row>
        <Row>
            <Product_Code>2090566WBC</Product_Code>
            <Full_Name>David A.F. Lewis, Esq.</Full_Name>
            <Last_First>LEWIS, DAVID</Last_First>
        </Row>
                    </presenters>
</Root>
0
QuintainTCommented:
Ok I think I've got it (I hope!)...

create procedure MySP
 @WEBCAST nvarchar(30)
as
begin
Select      1 Tag,
            Null Parent,
            Null [Root!1!webcast!Element],
            Null [webcast!2!Product_Code!Element],
            Null [webcast!2!description!Element],
            Null [webcast!2!begin_date!Element],
            Null [webcast!2!start_time!Element],
            Null [webcast!2!end_time!Element],
            Null [webcast!2!cle_credits!Element],
            Null [Root!1!presenters!Element],
            Null [presenters!3!row!Element],
            Null [row!4!Product_Code!Element],
            Null [row!4!Full_Name!Element],
            Null [row!4!Last_First!Element]
UNION ALL

Select      2 Tag,
            1,
            Null,
            Product_Code,
            description,
            begin_date,
            start_time,
            end_time,
            cle_credits,
               null,
      null,
      null,
      null,
      null

From      webv_program_catalog
Where      product_code = @WEBCAST
UNION ALL

Select top 1    3 Tag,
            1,
            Null,
      null,
      null,
      null,
      null,
      null,
      null,
      null,
           null,
            null,
            null,
            null

From      web_volunteers
Where      product_code = @WEBCAST

UNION ALL
Select      4 Tag,
            3,
            Null,
      null,
      null,
      null,
      null,
      null,
      null,
      null,
      null,
            Product_Code,
            Full_Name,
            Last_First

From      web_volunteers
Where      product_code = @WEBCAST
For XML Explicit
end
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeff_KingstonAuthor Commented:
This is going to be a life saver!!   Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.