Inserting SOAP formatted message data into a SQL table -- OPEN

I have displaying my SOAP message below. Could you show me what T-SQL
statement using OPENXML or otherwise I can use to import the data in this
SOAP message into various columns in a SQL table.
My SQL table has the following columns:
LogEntry varchar(255)
Message varchar(255)
Title varchar(255)
Category varchar(50)
Priority int
EventID int
Severity varchar(255)
MachineName varchar(50)
TimeStampVal datetime
ErrorMessages varchar(255)
ExtendedProperties varchar(255)
AppDomainName varchar(50)
ProcessID int
ProcessName varchar(255)
ThreadName varchar(50)
<SOAP-ENV:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<a1:LogEntry id="ref-1"
xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Microsoft.Practices.Enter
priseLibrary.Logging/Microsoft.Practices.EnterpriseLibrary.Logging%2C%20Vers
ion%3D1.0.0. 0%2C%20Culture%3Dneutral%2C%20PublicKeyT
oken%3Dnull">
<message id="ref-3">Msg successfully populated</message>
<title id="ref-4">Title1</title>
<category id="ref-5">Cat1</category>
<priority>100</priority>
<eventId>16</eventId>
<severity>Information</severity>
<machineName id="ref-6">MACH1</machineName>
<timeStamp>2005-05-20T15:24:18.4850496-05:00</timeStamp>
<errorMessages xsi:null="1"/>
<extendedProperties xsi:null="1"/>
<appDomainName id="ref-7">B1.exe</appDomainName>
<processId id="ref-8">3932</processId>
<processName id="ref-9">C:\B1\bin\Debug\B1.exe</processName>
<threadName xsi:null="1"/>
<win32ThreadId id="ref-10">244</win32ThreadId>
</a1:LogEntry>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
--
gg
"Graeme Malcolm" wrote:

> There are a couple of approaches. You could pass the entire SOAP XML doc t
o
> a stored procedure and use OPENXML to shred it into tables, or you could
> create an annotated XSD schema that maps the elements/attributes in your
> SOAP message to the tables/column in the database and use the SQLXML Bulk
> Load component.
> Neither of these approaches requires a SQLXML IIS site.
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "gudia" <gudia@.discussions.microsoft.com> wrote in message
> news:B85E4E76-A4FB-49A7-881A-A1795CE80728@.microsoft.com...
> Is configuring IIS to be used in conjunction with SQLXML a requirement for
> the question I am asking?
> My eventual goal is to insert the data present in the SOAP message into a
> SQL table.
> Please let me know how I can achieve this goal.
> Thanks
>
>Here's one way (I wasn't sure what you want in the LogEntry column, since
that's an XML element that contains all the others - so I used the id
attribute). This example uses a temporary table with the columns you
specified and I've hardcoded the SOAP message as a variable - in reality
you'd pass it to a stored procedure as a parameter. I suggest you take some
time to examine the documentation on OPENXML in Books Online to tweak this
to do exactly what you want it to.
USE Tempdb
CREATE TABLE #TestTable
(
LogEntry varchar(255),
Message varchar(255),
Title varchar(255),
Category varchar(50),
Priority int,
EventID int,
Severity varchar(255),
MachineName varchar(50),
TimeStampVal datetime,
ErrorMessages varchar(255),
ExtendedProperties varchar(255),
AppDomainName varchar(50),
ProcessID int,
ProcessName varchar(255),
ThreadName varchar(50)
)
DECLARE @.doc nvarchar(2000)
SET @.doc = '<SOAP-ENV:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<a1:LogEntry id="ref-1"
xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Microsoft.Practices.Enter
priseLibrary.Logging/Microsoft.Practices.EnterpriseLibrary.Logging%2C%20Vers
ion%3D1.0.0. 0%2C%20Culture%3Dneutral%2C%20PublicKeyT
oken%3Dnull">
<message id="ref-3">Msg successfully populated</message>
<title id="ref-4">Title1</title>
<category id="ref-5">Cat1</category>
<priority>100</priority>
<eventId>16</eventId>
<severity>Information</severity>
<machineName id="ref-6">MACH1</machineName>
<timeStamp>2005-05-20T15:24:18.4850496-05:00</timeStamp>
<errorMessages xsi:null="1"/>
<extendedProperties xsi:null="1"/>
<appDomainName id="ref-7">B1.exe</appDomainName>
<processId id="ref-8">3932</processId>
<processName id="ref-9">C:\B1\bin\Debug\B1.exe</processName>
<threadName xsi:null="1"/>
<win32ThreadId id="ref-10">244</win32ThreadId>
</a1:LogEntry>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
'
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.doc, '<ns
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Microsoft.Practices.Enter
priseLibrary.Logging/Microsoft.Practices.EnterpriseLibrary.Logging%2C%20Vers
ion%3D1.0.0. 0%2C%20Culture%3Dneutral%2C%20PublicKeyT
oken%3Dnull"/>'
INSERT #TestTable
SELECT * FROM OPENXML(@.hDoc, 'SOAP-ENV:Envelope/SOAP-ENV:Body/a1:LogEntry',
2)
WITH
(
LogEntry varchar(255) '@.id',
message varchar(255),
title varchar(255),
category varchar(50),
priority int,
eventId int,
severity varchar(255),
machineName varchar(50),
timeStampVal datetime,
errorMessages varchar(255),
extendedProperties varchar(255),
appDomainName varchar(50),
processId int,
processName varchar(255),
threadName varchar(50)
)
SELECT * FROM #TestTable
DROP TABLE #TestTable
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"gudia" <gudia@.discussions.microsoft.com> wrote in message
news:B85C3731-29C4-4503-9A4A-237B7F83A5B7@.microsoft.com...
I have displaying my SOAP message below. Could you show me what T-SQL
statement using OPENXML or otherwise I can use to import the data in this
SOAP message into various columns in a SQL table.
My SQL table has the following columns:
LogEntry varchar(255)
Message varchar(255)
Title varchar(255)
Category varchar(50)
Priority int
EventID int
Severity varchar(255)
MachineName varchar(50)
TimeStampVal datetime
ErrorMessages varchar(255)
ExtendedProperties varchar(255)
AppDomainName varchar(50)
ProcessID int
ProcessName varchar(255)
ThreadName varchar(50)
<SOAP-ENV:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<a1:LogEntry id="ref-1"
xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Microsoft.Practices.Enter
priseLibrary.Logging/Microsoft.Practices.EnterpriseLibrary.Logging%2C%20Vers
ion%3D1.0.0. 0%2C%20Culture%3Dneutral%2C%20PublicKeyT
oken%3Dnull">
<message id="ref-3">Msg successfully populated</message>
<title id="ref-4">Title1</title>
<category id="ref-5">Cat1</category>
<priority>100</priority>
<eventId>16</eventId>
<severity>Information</severity>
<machineName id="ref-6">MACH1</machineName>
<timeStamp>2005-05-20T15:24:18.4850496-05:00</timeStamp>
<errorMessages xsi:null="1"/>
<extendedProperties xsi:null="1"/>
<appDomainName id="ref-7">B1.exe</appDomainName>
<processId id="ref-8">3932</processId>
<processName id="ref-9">C:\B1\bin\Debug\B1.exe</processName>
<threadName xsi:null="1"/>
<win32ThreadId id="ref-10">244</win32ThreadId>
</a1:LogEntry>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
--
gg
"Graeme Malcolm" wrote:

> There are a couple of approaches. You could pass the entire SOAP XML doc
> to
> a stored procedure and use OPENXML to shred it into tables, or you could
> create an annotated XSD schema that maps the elements/attributes in your
> SOAP message to the tables/column in the database and use the SQLXML Bulk
> Load component.
> Neither of these approaches requires a SQLXML IIS site.
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "gudia" <gudia@.discussions.microsoft.com> wrote in message
> news:B85E4E76-A4FB-49A7-881A-A1795CE80728@.microsoft.com...
> Is configuring IIS to be used in conjunction with SQLXML a requirement for
> the question I am asking?
> My eventual goal is to insert the data present in the SOAP message into a
> SQL table.
> Please let me know how I can achieve this goal.
> Thanks
>
>|||Thanks so much Graeme. That worked.
Appreciate your help.
"Graeme Malcolm" wrote:

> Here's one way (I wasn't sure what you want in the LogEntry column, since
> that's an XML element that contains all the others - so I used the id
> attribute). This example uses a temporary table with the columns you
> specified and I've hardcoded the SOAP message as a variable - in reality
> you'd pass it to a stored procedure as a parameter. I suggest you take so
me
> time to examine the documentation on OPENXML in Books Online to tweak this
> to do exactly what you want it to.
> USE Tempdb
> CREATE TABLE #TestTable
> (
> LogEntry varchar(255),
> Message varchar(255),
> Title varchar(255),
> Category varchar(50),
> Priority int,
> EventID int,
> Severity varchar(255),
> MachineName varchar(50),
> TimeStampVal datetime,
> ErrorMessages varchar(255),
> ExtendedProperties varchar(255),
> AppDomainName varchar(50),
> ProcessID int,
> ProcessName varchar(255),
> ThreadName varchar(50)
> )
> DECLARE @.doc nvarchar(2000)
> SET @.doc = '<SOAP-ENV:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
> xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0"
> SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
> <SOAP-ENV:Body>
> <a1:LogEntry id="ref-1"
> xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Microsoft.Practices.Ent
erpriseLibrary.Logging/Microsoft.Practices.EnterpriseLibrary.Logging%2C%20Ve
rsion%3D1.0.0. 0%2C%20Culture%3Dneutral%2C%20PublicKeyT
oken%3Dnull">
> <message id="ref-3">Msg successfully populated</message>
> <title id="ref-4">Title1</title>
> <category id="ref-5">Cat1</category>
> <priority>100</priority>
> <eventId>16</eventId>
> <severity>Information</severity>
> <machineName id="ref-6">MACH1</machineName>
> <timeStamp>2005-05-20T15:24:18.4850496-05:00</timeStamp>
> <errorMessages xsi:null="1"/>
> <extendedProperties xsi:null="1"/>
> <appDomainName id="ref-7">B1.exe</appDomainName>
> <processId id="ref-8">3932</processId>
> <processName id="ref-9">C:\B1\bin\Debug\B1.exe</processName>
> <threadName xsi:null="1"/>
> <win32ThreadId id="ref-10">244</win32ThreadId>
> </a1:LogEntry>
> </SOAP-ENV:Body>
> </SOAP-ENV:Envelope>
> '
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.doc, '<ns
> xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Microsoft.Practices.Ent
erpriseLibrary.Logging/Microsoft.Practices.EnterpriseLibrary.Logging%2C%20Ve
rsion%3D1.0.0. 0%2C%20Culture%3Dneutral%2C%20PublicKeyT
oken%3Dnull"/>'
> INSERT #TestTable
> SELECT * FROM OPENXML(@.hDoc, 'SOAP-ENV:Envelope/SOAP-ENV:Body/a1:LogEntry'
,
> 2)
> WITH
> (
> LogEntry varchar(255) '@.id',
> message varchar(255),
> title varchar(255),
> category varchar(50),
> priority int,
> eventId int,
> severity varchar(255),
> machineName varchar(50),
> timeStampVal datetime,
> errorMessages varchar(255),
> extendedProperties varchar(255),
> appDomainName varchar(50),
> processId int,
> processName varchar(255),
> threadName varchar(50)
> )
> SELECT * FROM #TestTable
> DROP TABLE #TestTable
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "gudia" <gudia@.discussions.microsoft.com> wrote in message
> news:B85C3731-29C4-4503-9A4A-237B7F83A5B7@.microsoft.com...
> I have displaying my SOAP message below. Could you show me what T-SQL
> statement using OPENXML or otherwise I can use to import the data in this
> SOAP message into various columns in a SQL table.
> My SQL table has the following columns:
> LogEntry varchar(255)
> Message varchar(255)
> Title varchar(255)
> Category varchar(50)
> Priority int
> EventID int
> Severity varchar(255)
> MachineName varchar(50)
> TimeStampVal datetime
> ErrorMessages varchar(255)
> ExtendedProperties varchar(255)
> AppDomainName varchar(50)
> ProcessID int
> ProcessName varchar(255)
> ThreadName varchar(50)
> <SOAP-ENV:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
> xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0"
> SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
> <SOAP-ENV:Body>
> <a1:LogEntry id="ref-1"
> xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Microsoft.Practices.Ent
erpriseLibrary.Logging/Microsoft.Practices.EnterpriseLibrary.Logging%2C%20Ve
rsion%3D1.0.0. 0%2C%20Culture%3Dneutral%2C%20PublicKeyT
oken%3Dnull">
> <message id="ref-3">Msg successfully populated</message>
> <title id="ref-4">Title1</title>
> <category id="ref-5">Cat1</category>
> <priority>100</priority>
> <eventId>16</eventId>
> <severity>Information</severity>
> <machineName id="ref-6">MACH1</machineName>
> <timeStamp>2005-05-20T15:24:18.4850496-05:00</timeStamp>
> <errorMessages xsi:null="1"/>
> <extendedProperties xsi:null="1"/>
> <appDomainName id="ref-7">B1.exe</appDomainName>
> <processId id="ref-8">3932</processId>
> <processName id="ref-9">C:\B1\bin\Debug\B1.exe</processName>
> <threadName xsi:null="1"/>
> <win32ThreadId id="ref-10">244</win32ThreadId>
> </a1:LogEntry>
> </SOAP-ENV:Body>
> </SOAP-ENV:Envelope>
> --
> gg
>
> "Graeme Malcolm" wrote:
>
>
>

Inserting records and displaying them

Before I start driving myself nuts, I'd like to make sure my approach is correct.

I want to create a simple job posting board.
I have a text boxes for company name, email, job title, and job description, and a submit button.
I created a table in my database called "JobPostings", with columns called "CoName", "CoEmail", "JobTitle", and "JobDesc"

When someone fills out the fields and clicks submit, it will insert the new records.

So,
1. Is this the correct approach so far?

2. What is the best way to display the job listings? A grid view?

3. At submit time, how can I include that day's date?

4. How can I get the records in the database to delete after 90 days?

Thanks.

1. If it did what you want, it's correct to you

2. You can try SqlDataSource+GridView, see

3. You can add a datetime column (e.g. CreateDate) to the table, whose default value is getdate() so that when a new record being inserted you don't need to specify date for the column:

ALTER TABLE mytable ADD CreateDate DATETIME DEFAULT GetDate()

4. You can use such command to delete records older than 90 days:

DELETE FROM mytable WHERE DATEDIFF(d,CreateDate,GETDATE())>90