Create XML File From SQL Table 1

[esilva002]

Hello World! I have a bit of an issue I need my table in sql to be exported to an XML file. I have an example of the XML file and created my table to look like the file. Here is what my final XML is supposed to look like:

<?xml version="1.0" encoding="windows-1252"?>
<Root>
<ListPreDef>
<bm TYPE="1" COLOR="-1">
<DOCKNO>
<![CDATA[CRB1002061]]>
</DOCKNO>
<JUDGE>
<![CDATA[MAC]]>
</JUDGE>
<PLAINTIFF>
<![CDATA[]]>
</PLAINTIFF>
<PLAINMAN>
<![CDATA[]]>
</PLAINMAN>
<DEFENSE>
<![CDATA[]]>
</DEFENSE>
<DEFENDANT>
<![CDATA[Acton, Heidi Y]]>
</DEFENDANT>
<CMNT>
<![CDATA[]]>
</CMNT>
</bm>
<bm TYPE="1" COLOR="-1">
<DOCKNO>
<![CDATA[CRA1001371]]>
</DOCKNO>
<JUDGE>
<![CDATA[MAC]]>
</JUDGE>
<PLAINTIFF>
<![CDATA[]]>
</PLAINTIFF>
<PLAINMAN>
<![CDATA[]]>
</PLAINMAN>
<DEFENSE>
<![CDATA[Lake Co Public Defender]]>
</DEFENSE>
<DEFENDANT>
<![CDATA[Bailey, Melanie R]]>
</DEFENDANT>
<CMNT>
<![CDATA[***IN JAIL***]]>
</CMNT>
</bm>

I built a table that has the columns of:
DOCKNO, JUDGE, PLAINTIFF, PLAINMAN, DEFENSE, DEFENDANT, CMNT

Do I need a unique Identifier? How would I go about doing this? THANKs FOR ANY INPUT, IT IS REALL APPRECIATED!!!!

 

[tsuji]

The xml document will convey the same information with text node being in cdata section or not. The info is stored the same escaped format of the five entities. This is to suggest you should have no special reason to insist on output text nodes as cdata sections.

If you feel comfortable with that, then, you can do this using only for xml path. (Otherwise, for xml explicit would be needed.)

Suppose the table be "x".
[tt]
select(
select
1 '@TYPE',
-1 '@COLOR',
*
from x for xml path('bm'), type
) as 'ListRefDef'
for xml path('Root'), type
[/tt]

 

[tsuji]

amendment
[tt][blue]ListRefDef[/blue][/tt] should be read [tt][red]ListPreDef[/red][/tt] to be exactly what is asked in the original post.

 

[esilva002]

Works Great Thanks A Lot!!! but how would I go about taking that record and saving it to a file on my server?

 

[tsuji]

>go about taking that record and saving it to a file on my server
You could take a look of the bcp utility.

http://msdn.microsoft.com/en-us/library/ms162802.aspx

And use xp_cmdshell to exec a bcp command line to finish off the task.

http://msdn.microsoft.com/en-us/library/ms175046.aspx

 

[esilva002]

Ok I took the supplied information and came up with this in a stored procedure:
SET NOCOUNT ON;
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @FileName = '\\primary\Rockftp\Judge1.xml'
SET @bcpCommand = 'select(select 1 ''@TYPE'', -1 ''@COLOR'', * from tblBIS_ExportJudge1 for xml path(''bm''), type) as ''ListPreDef'' for xml path(''Root''), type'
SET @bcpCommand = @bcpCommand + @FileName + '" -T -c -x'
EXEC RockwareEXPORT..xp_cmdshell @bcpCommand

When I run it I get 3 columns output:
'select' is not recognized as an internal or external command,
operable program or batch file.
NULL

A thought that crossed my mind is that the select statement that tsuji created is already putting the xml tags in the output, could that be giving my bcp command trouble?

 

[esilva002]

I still don't know how to take it from a record to an actual file, but I also have another problem where my XML Header tag is missing. <?xml version="1.0" encoding="windows-1252"?>

 

[esilva002]

hey hey hey!

I used code that tsuji supplied me but it gives me an xml file that looks like this(I also supply the code below):
<?xml version="1.0" encoding="windows-1252"?>
<Root>
<ListPreDef>
<bm TYPE="1" COLOR="-1">
<DockNo>675303</DockNo>
<Judge>Judge</Judge>
<Plaintiff>TUCKER, DAVID P JR. 1</Plaintiff>
<Plainman/>
<Defense/>
<Defendant>COX, CORETTA 1</Defendant>
<CMNT>pro se with children</CMNT>
</bm>
<ListPreDef>
</Root>

Code:
select '\\lawserver\recordcp\Judge1.xml' filename
, (select '<?xml version="1.0" encoding="windows-1252"?>' + cast((select (
select
1 '@TYPE',
-1 '@COLOR',
*
from tblBIS_ExportJudge1 for xml path('bm'), type
) as 'ListPreDef'
for xml path('Root'), type) as varchar(max))) data

Now my problem lies within the data. becuase I am looking for something more like this:
<?xml version="1.0" encoding="windows-1252"?>
<Root>
<ListPreDef>
<bm TYPE="1" COLOR="-1">
<DOCKNO>
<![CDATA[CRB1002061]]>
</DOCKNO>
<JUDGE>
<![CDATA[MAC]]>
</JUDGE>
<PLAINTIFF>
<![CDATA[]]>
</PLAINTIFF>
<PLAINMAN>
<![CDATA[]]>
</PLAINMAN>
<DEFENSE>
<![CDATA[]]>
</DEFENSE>
<DEFENDANT>
<![CDATA[Acton, Heidi Y]]>
</DEFENDANT>
<CMNT>
<![CDATA[]]>
</CMNT>
</bm>
</ListPreDef>
</Root>
Here is my question how do get <![CDATA[]]> around my data? Do I have to add an xml tag?