Convert SQL table to XML

Question

Convert SQL table to XML

Viewing 6 posts - 16 through 20 (of 20 total)

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

devesh.hassani SSChasing Mays Points: 600 More actions · Answer 1

Hi Jeff,

I got a sample file from configuration team and them want the data output in the given format to upload it. The sample file that I receive was exported from an application used to maintain configuration required for the system.

Thanks,

DH

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 2

devesh.hassani (10/7/2015)
Hi Jeff,
I got a sample file from configuration team and them want the data output in the given format to upload it. The sample file that I receive was exported from an application used to maintain configuration required for the system.
Thanks,
DH

Based on the above, I may have been incorrect in my original guess, so bear with me for a minute while I try to understand your requirements.

1. You have a table in a database with the information you need in it.

2. You need to select and format that data.

3. You need to "export" that formatted data.

4. Someone else needs to "import" that data into something else.

I have some questions concerning those requirements so that we can get'er done right the first time... 🙂

1. Let's start at the end goal, which is Item #4 above.

1.b. If it's not being imported into a spreadsheet, then they just need a text file?

1.c. In either case, do they [font="Arial Black"]really [/font]need the XML formatted data (as in your original example) or will something simpler like a TSV file (Tab Separated Values) work?

2. Going back to Item #3 and regardless of the format of the data we end up with, you want SQL Server to "export" the formatted data. There are a couple of "keys" to this part of the problem that will dictate how it needs to be done.

2.b. What will be the "thing" that triggers the stored procedure to executed?

2.c. If the output must be directed to a physical file, can xp_CmdShell be used (we CAN do that safely) and, if not, can SQLCmd be used?

2.d. If the output must be a physical file, will the location of that file be on the SQL Server itself (not recommended) or on some other machine? If on some other machine, will it always be the same machine and folder (recommended) or will the target machine and/or folder vary (not recommended)?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

devesh.hassani SSChasing Mays Points: 600 More actions · Answer 3

Hi Jeff,

Please find my reply

1. Let's start at the end goal, which is Item #4 above.

1.a. What are they importing the data to? For example, is it a spreadsheet that they're going to import the data to?

Data will be upload to another system using portal. So the XML file will be used by an application for bulk upload.

1.b. If it's not being imported into a spreadsheet, then they just need a text file?

Application requires file to be in mentioned format.

1.c. In either case, do they really need the XML formatted data (as in your original example) or will something simpler like a TSV file (Tab Separated Values) work?

Yes we do need XML formatted data.

2. Going back to Item #3 and regardless of the format of the data we end up with, you want SQL Server to "export" the formatted data. There are a couple of "keys" to this part of the problem that will dictate how it needs to be done.

2.a. The output needs to be exported to a physical file, correct? Or will the output be consumed directly by whatever is calling the proc?

Yes it should be physical file

2.b. What will be the "thing" that triggers the stored procedure to executed?

This is one time task and will be run manually. So it can be simply script which returns that data in XML format and we can copy the data into physical file manually or using cmd.

2.c. If the output must be directed to a physical file, can xp_CmdShell be used (we CAN do that safely) and, if not, can SQLCmd be used?

SQL cmd can be used

2.d. If the output must be a physical file, will the location of that file be on the SQL Server itself (not recommended) or on some other machine? If on some other machine, will it always be the same machine and folder (recommended) or will the target machine and/or folder vary (not recommended)?

Location will be machine from which I am connecting to SQL instance. suppose if I am connecting to SQL server from my local system then file should be placed on my local machine.

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

In that case, especially since it's a "one off" task, I have to agree with Mister Magoo above in saying it's not worth trying to do this using any form of XML manipulation. It's also not worth trying to automate the export.

Rather, do the following from SSMS...

1. Borrowing heavily on Magoo's code, load the following code into SSMS but don't run it, yet. Of course, you'll need to change the column names and the table name but this code will run with Magoo's test data that he posted.

--===== Suppress the auto-display of rowcounts

SET NOCOUNT ON

;

--===== Create the output for the file exactly as they asked for it

SELECT REPLACE(REPLACE(REPLACE('

<Row>

<Cell ss:StyleID="s22">

<Data ss:Type="String">[Column A]</Data>

</Cell>

<Cell ss:StyleID="s22">

<Data ss:Type="String">[Column B]</Data>

</Cell>

<Cell ss:StyleID="s22">

<Data ss:Type="String">[Column C]</Data>

</Cell>

</Row>'

,'[Column A]',ISNULL([Column A],''))

,'[Column B]',ISNULL([Column B],''))

,'[Column C]',ISNULL([Column C],''))

FROM #Temp

;

2. Change the output type to "Results to File".

3. Run the query.

4. It'll ask you where you want to store the file and what you want to name it. Change the "Save as Type" to "All Files(*.*)", select the directory where you want to store the file, type the name.extension you want for the "File name:", and then click on the "Save" button.

5. Open the file using Notepad or your favorite basic text editor and delete the dashed line.

6. Save the file and close Notepad.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 5

anthony.green (10/6/2015)
How often does this need to run?
Very ad-hoc you could look at the Import/Export Wizard
Everyday/hour etc I would look at SSIS
Also thanks to Jeff, hadn't seen spreadsheet XML like that will be one ingrained into the recesses of my mind

Just to be sure, it was a total SWAG on my part and, based on the new information, likely has nothing to do with a spreadsheet.

As for the Import/Export Wizard or SSIS, I'm thinking that's way overkill for this especially since I'd have to enable SSIS (I don't ever use it) 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 6

Jeff Moden (10/7/2015)
In that case, especially since it's a "one off" task, I have to agree with Mister Magoo above in saying it's not worth trying to do this using any form of XML manipulation. It's also not worth trying to automate the export.
Rather, do the following from SSMS...
1. Borrowing heavily on Magoo's code, load the following code into SSMS but don't run it, yet. Of course, you'll need to change the column names and the table name but this code will run with Magoo's test data that he posted.
--===== Suppress the auto-display of rowcounts
SET NOCOUNT ON
;
--===== Create the output for the file exactly as they asked for it
SELECT REPLACE(REPLACE(REPLACE('
<Row>
<Cell ss:StyleID="s22">
<Data ss:Type="String">[Column A]</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">[Column B]</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">[Column C]</Data>
</Cell>
</Row>'
,'[Column A]',ISNULL([Column A],''))
,'[Column B]',ISNULL([Column B],''))
,'[Column C]',ISNULL([Column C],''))
FROM #Temp
;
2. Change the output type to "Results to File".
3. Run the query.
4. It'll ask you where you want to store the file and what you want to name it. Change the "Save as Type" to "All Files(*.*)", select the directory where you want to store the file, type the name.extension you want for the "File name:", and then click on the "Save" button.
5. Open the file using Notepad or your favorite basic text editor and delete the dashed line.
6. Save the file and close Notepad.

I almost forgot... make sure the column width in the output settings is set to 8192 under {Tools}{Options}{Query Results}{SQL Server}{Results to Text}.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)