Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scottdg
Starting Member
20 Posts |
 Posted - 2011-04-27 : 16:35:52
|
| I am very new to sql programming and have only written some simple queries but I am presented with a situation at work and need to know how to handle it. To give a brief background, my company is merging with another and we have different databases obviously. We are going to run them both for now and share data. Someone at the other company suggested an XML schema that can be use to initiate and test a data exchange between the two systems. He gave the following example:<?xml version="1.0"?><catalog> <member_id="1234567"> <name>scottdg</name> <mbrstatus>Active</mbrstatus> <sections>1,4,5</sections> <expdate>04-30-2011</expdate> </member></catalog>Now I can get an XML file in sql by using the "for xml auto" command but it is not in this format. I am going to have to join 2-3 tables together to get all the data I need which isn't a problem. The only thing I am not sure of is how to get it in this format. Actually there is one other thing...is there a way to make this a dynamic file that is updated automatically and does not require any human interaction to create?Thanks in advance. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-27 : 16:38:48
|
| You'll want to look at FOR XML PATH, it's pretty easy to have it build XML in the format you need. As far as generating the file, you can use SSIS or possibly bcp to generate a file and make a scheduled job out of it. As long as the query doesn't have to change, you can turn it into a stored procedure and run it as part of the job. |
 |
|
|
scottdg
Starting Member
20 Posts |
Posted - 2011-04-27 : 16:43:32
|
| Thanks Rob. I will look into that. I am not familiar with SSIS or bcp ( I really am a novice). Can you recommend some good resources to get me started? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
scottdg
Starting Member
20 Posts |
Posted - 2011-04-28 : 11:20:20
|
| Thanks again. |
|
|
|
scottdg
Starting Member
20 Posts |
Posted - 2011-05-20 : 12:06:20
|
| I finally got around to trying this and when I use FOR XML PATH and the end of my query I get a result similar to the following:<row> <MASTER_CUSTOMER_ID>XXXXXXXXXXXX</MASTER_CUSTOMER_ID> <NAME_PREFIX>Mr</NAME_PREFIX> <FIRST_NAME>Charles</FIRST_NAME> <MIDDLE_NAME>P</MIDDLE_NAME> <LAST_NAME>Abraham</LAST_NAME> <NAME_CREDENTIALS>Esq.</NAME_CREDENTIALS> <ADDRESS_1>123 Main St.</ADDRESS_1> <CITY>East Bumble</CITY> <STATE>NJ</STATE> <POSTAL_CODE>01234</POSTAL_CODE> <product_code>REG_MBR</product_code> </row><row> <MASTER_CUSTOMER_ID>XXXXXXXXXXXX</MASTER_CUSTOMER_ID> <NAME_PREFIX>Mr</NAME_PREFIX> <FIRST_NAME>Charles</FIRST_NAME> <MIDDLE_NAME>P</MIDDLE_NAME> <LAST_NAME>Abraham</LAST_NAME> <NAME_CREDENTIALS>Esq.</NAME_CREDENTIALS> <ADDRESS_1>123 Main St.</ADDRESS_1> <CITY>East Bumble</CITY> <STATE>NJ</STATE> <POSTAL_CODE>01234</POSTAL_CODE> <product_code>SEC_RPP</product_code>The difference is in the last column displayed which is product code. Our members have a general membership in this case REG_MBR and they can have section memberships which is in the second record. These membership types are differentiated not only by PRODUCT_CODE but by a column in another table called LEVEL which can be equal to either MEMBERSHIP or SECTION. I need to data to display in a format similar to the following:<catalog> <member CUSTOMER_ID = "XXXXXXXXXXXX"> <NAME_PREFIX>Mr</NAME_PREFIX> <FIRST_NAME>Charles</FIRST_NAME> <MIDDLE_NAME>P</MIDDLE_NAME> <LAST_NAME>Abraham</LAST_NAME> <NAME_CREDENTIALS>Esq.</NAME_CREDENTIALS> <ADDRESS_1>123 Main St.</ADDRESS_1> <CITY>East Bumble</CITY> <STATE>NJ</STATE> <POSTAL_CODE>01234</POSTAL_CODE> <member product_code>REG_MBR</member product_code> <section product_code>SEC_RPP, SEC_DSP, SEC_ADM</section product_code> </member></catalog> You can see that I need the section product codes listed next to each other.Any ideas? As I said in my original post I am really a novice. |
|
|
|
|
|
|
XML Feed