I have a SOAP formatted message. I am passing this message as a string from
a
C# client to a SQL Stored Procedure.
I would like to read this SOAP formatted message and store the data in the
SOAP message in appropriate columns in a SQL table. How can I achieve this?
Can OPENXML be used for this purpose?
--
ggCould you just store in a nvarchar(max) ?
William Stacey [MVP]
"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|||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
Showing posts with label openxml. Show all posts
Showing posts with label openxml. Show all posts
Friday, February 24, 2012
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:
>
>
>
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 that don't already exist
There is a good article at
http://support.microsoft.com/defaul...kb;en-us;315968 that shows ho
w
to use OPENXML to update records that exist and add new records that don't t
o
a table.
The question is how do you modify the SQL code to work with a table with a
three column primary key? The IN statement only works when selecting a
single column.
Any help would be much appreciated.
Thanks,
OldmanHi
You can try this way
Insert Into Employee
SELECT EmployeeId, FirstName, LastName
FROM OPENXML (@.hdoc, '/NewDataSet/Employee',1)
WITH (EmployeeId Integer, FirstName varchar(100), LastName varchar(100))
XMLEmployee
INNER JOIN Employee
Where Employee.EmployeeId <> XMLEmployee.EmployeeID AND
Employee.FirstName <> XMLEmployee.FirstName AND
Employee.LastName <> XMLEmployee.LastName
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Oldman" wrote:
> There is a good article at
> http://support.microsoft.com/defaul...kb;en-us;315968 that shows
how
> to use OPENXML to update records that exist and add new records that don't
to
> a table.
> The question is how do you modify the SQL code to work with a table with a
> three column primary key? The IN statement only works when selecting a
> single column.
> Any help would be much appreciated.
> Thanks,
> Oldman|||> The question is how do you modify the SQL code to work with a table with a
> three column primary key? The IN statement only works when selecting a
> single column.
You can use NOT EXISTS instead of NOT IN:
WHERE NOT EXISTS
(SELECT *
FROM Employee e
WHERE e.Col1 = XMLEmployee.Col1 AND
e.Col2 = XMLEmployee.Col2 AND
e.Col3 = XMLEmployee.Col3)
Hope this helps.
Dan Guzman
SQL Server MVP
"Oldman" <Oldman@.discussions.microsoft.com> wrote in message
news:E0FC51E3-8305-4641-AA72-D470984B9510@.microsoft.com...
> There is a good article at
> http://support.microsoft.com/defaul...kb;en-us;315968 that shows
> how
> to use OPENXML to update records that exist and add new records that don't
> to
> a table.
> The question is how do you modify the SQL code to work with a table with a
> three column primary key? The IN statement only works when selecting a
> single column.
> Any help would be much appreciated.
> Thanks,
> Oldman|||Let us assume that the pk is (employeeid, firstname, lastname), then you can
do:
insert into employee
select
employeeid,
firstname,
lastname
from
openxml (@.hdoc, '/newdataset/employee',1)
with (employeeid integer, firstname varchar(100), lastname varchar(100))
as x
left join
employee as e
on
e.employeeid = x.employeeid and
e.firstname = x.firstname and
e.lastname = x.lastname
where
e.employeeid is null
and e.firstname is null
and e.lastname is null
go
AMB
"Oldman" wrote:
> There is a good article at
> http://support.microsoft.com/defaul...kb;en-us;315968 that shows
how
> to use OPENXML to update records that exist and add new records that don't
to
> a table.
> The question is how do you modify the SQL code to work with a table with a
> three column primary key? The IN statement only works when selecting a
> single column.
> Any help would be much appreciated.
> Thanks,
> Oldman|||Thanks guys for your answers. Dan that worked!
Now that you told me I'm smacking my head because I knew of the EXISTS
keyword.
Thanks again.
"Dan Guzman" wrote:
> You can use NOT EXISTS instead of NOT IN:
> WHERE NOT EXISTS
> (SELECT *
> FROM Employee e
> WHERE e.Col1 = XMLEmployee.Col1 AND
> e.Col2 = XMLEmployee.Col2 AND
> e.Col3 = XMLEmployee.Col3)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Oldman" <Oldman@.discussions.microsoft.com> wrote in message
> news:E0FC51E3-8305-4641-AA72-D470984B9510@.microsoft.com...
>
>
http://support.microsoft.com/defaul...kb;en-us;315968 that shows ho
w
to use OPENXML to update records that exist and add new records that don't t
o
a table.
The question is how do you modify the SQL code to work with a table with a
three column primary key? The IN statement only works when selecting a
single column.
Any help would be much appreciated.
Thanks,
OldmanHi
You can try this way
Insert Into Employee
SELECT EmployeeId, FirstName, LastName
FROM OPENXML (@.hdoc, '/NewDataSet/Employee',1)
WITH (EmployeeId Integer, FirstName varchar(100), LastName varchar(100))
XMLEmployee
INNER JOIN Employee
Where Employee.EmployeeId <> XMLEmployee.EmployeeID AND
Employee.FirstName <> XMLEmployee.FirstName AND
Employee.LastName <> XMLEmployee.LastName
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Oldman" wrote:
> There is a good article at
> http://support.microsoft.com/defaul...kb;en-us;315968 that shows
how
> to use OPENXML to update records that exist and add new records that don't
to
> a table.
> The question is how do you modify the SQL code to work with a table with a
> three column primary key? The IN statement only works when selecting a
> single column.
> Any help would be much appreciated.
> Thanks,
> Oldman|||> The question is how do you modify the SQL code to work with a table with a
> three column primary key? The IN statement only works when selecting a
> single column.
You can use NOT EXISTS instead of NOT IN:
WHERE NOT EXISTS
(SELECT *
FROM Employee e
WHERE e.Col1 = XMLEmployee.Col1 AND
e.Col2 = XMLEmployee.Col2 AND
e.Col3 = XMLEmployee.Col3)
Hope this helps.
Dan Guzman
SQL Server MVP
"Oldman" <Oldman@.discussions.microsoft.com> wrote in message
news:E0FC51E3-8305-4641-AA72-D470984B9510@.microsoft.com...
> There is a good article at
> http://support.microsoft.com/defaul...kb;en-us;315968 that shows
> how
> to use OPENXML to update records that exist and add new records that don't
> to
> a table.
> The question is how do you modify the SQL code to work with a table with a
> three column primary key? The IN statement only works when selecting a
> single column.
> Any help would be much appreciated.
> Thanks,
> Oldman|||Let us assume that the pk is (employeeid, firstname, lastname), then you can
do:
insert into employee
select
employeeid,
firstname,
lastname
from
openxml (@.hdoc, '/newdataset/employee',1)
with (employeeid integer, firstname varchar(100), lastname varchar(100))
as x
left join
employee as e
on
e.employeeid = x.employeeid and
e.firstname = x.firstname and
e.lastname = x.lastname
where
e.employeeid is null
and e.firstname is null
and e.lastname is null
go
AMB
"Oldman" wrote:
> There is a good article at
> http://support.microsoft.com/defaul...kb;en-us;315968 that shows
how
> to use OPENXML to update records that exist and add new records that don't
to
> a table.
> The question is how do you modify the SQL code to work with a table with a
> three column primary key? The IN statement only works when selecting a
> single column.
> Any help would be much appreciated.
> Thanks,
> Oldman|||Thanks guys for your answers. Dan that worked!
Now that you told me I'm smacking my head because I knew of the EXISTS
keyword.
Thanks again.
"Dan Guzman" wrote:
> You can use NOT EXISTS instead of NOT IN:
> WHERE NOT EXISTS
> (SELECT *
> FROM Employee e
> WHERE e.Col1 = XMLEmployee.Col1 AND
> e.Col2 = XMLEmployee.Col2 AND
> e.Col3 = XMLEmployee.Col3)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Oldman" <Oldman@.discussions.microsoft.com> wrote in message
> news:E0FC51E3-8305-4641-AA72-D470984B9510@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)