Solved

vb.net XML SQL

Posted on 2014-03-13
3
379 Views
Last Modified: 2014-03-14
The below function creates an XML document that is then written to disk.  Also below is an T-SQL command that converts part of the XML document into an SQL table.  It all works.  

QUESTION:  Can the step of writing the XML document to disk be skipped and the XML document object be sent directly to the T-SQL statement that is sent to SQL 2008?
    Public Function xCMP() As XDocument

        Return _
         <?xml version="1.0"?>
         <CMPDocument>
             <Title>EPOWERdoc - CMP Document</Title>
             <Client>
                 <DateProcessed><%= Dt2Db(Now()) %></DateProcessed>
                 <CliNo><%= Clino %></CliNo>
                 <CliServerName><%= CliServerName %></CliServerName>
                 <RAMTotal><%= RAMTotal %></RAMTotal>
                 <RAMSQL><%= RAMSQL %></RAMSQL>
                 <LastFDBUpdate><%= LastFDBUpdate %></LastFDBUpdate>
             </Client>
             <CMPData>
                 <%= xDiskDrives %>
                 <%= xVolume %>
                 <%= xExecutables %>
                 <%= xEPDApplicationErrors %>
                 <%= xEventLog %>
             </CMPData>
         </CMPDocument>
    End Function

Open in new window


---------------------------  T-SQL ----------------------------
DECLARE @x xml
SELECT @x = SQLs
FROM OPENROWSET (BULK 'C:\EPD\EPD315\Bin\CMP\1_201403130224.xml', SINGLE_BLOB) AS Solution(SQLs)

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

SELECT * FROM OPENXML (@hdoc, '/CMPDocument/CMPData/DiskDrives', 2)
WITH (
      [Drive] varchar(500)
      ,TotalSpace varchar(500)
      ,FreeSpace varchar(500)
      )

EXEC sp_xml_removedocument @hdoc

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

Open in new window

0
Comment
Question by:dastaub
3 Comments
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 500 total points
ID: 39928464
Hi,

Since SQL has an xml data type, you may add it to the procedure's parameters and then directly pass an xml to the sp instead of writting it to the disk.

For calling it through your program you may check the following link:
http://msdn.microsoft.com/en-us/library/34e4kcbw(v=vs.110).aspx

Giannis
0
 

Author Comment

by:dastaub
ID: 39929567
Thank You
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question