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:
>
>
>