XML to SQL Table through OpenXML

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

XML to SQL Table through OpenXML

Author

Topic

ajmaly
Starting Member

11 Posts

Posted - 2010-07-20 : 08:23:40

I having below XML,

<PDI_Approval_Message>
<header>
<SourceSystem value ="WF" Name ="Workforce"></SourceSystem>
<TransactionType value="WF_TS" Name="WF-TimeSheet" ></TransactionType>
<RequestType value ="Submit" Name="Action"></RequestType>
<SubmittedOn Value="02/06/2010" Name=""></SubmittedOn>
<SubmittedBy Value="pdi/vmuser3" Name="hannok"></SubmittedBy>
</header>
<Transactions>
<Transaction ID = “ WF_b738b9b1-5cb1-48e3-98cd-044065d4ac22”/>
<Attributes>
<Attribute value = “0625” Name = “Emp_ID”/>
<Attribute value = “03/06/2010” Name = “Week_Ending”/>
<Attribute value = “0034” Name = “Site_ID”/>
<Attribute value = “15” Name = “TotalHrs”/>
<Attribute value = “10” Name = “Total_Overtime”/>
</Attributes>

<Attachments>
<Attachment value = “/test/file.doc” Name = “timesheetdocument”>
<Attachment value = “/test/file1.doc” Name = “approvalletter”>
</Attachments>
</Transaction >

<Transaction ID = “ WF_728d1ec7-de8f-4575-86cb-3cbb37b133a4”/>
<Attribute value = “06251” Name = “Emp_ID”/>
<Attribute value = “03/06/2010” Name = “Week_Ending”/>
<Attribute value = “0034” Name = “Site_ID”/>
<Attribute value = “20” Name = “TotalHrs”/>
<Attribute value = “10” Name = “Total_Overtime”/>
</Transaction >

<Transaction ID = “ WF_7ae6ec19-c677-49a1-b87c-5887d26889ba”/>
<Attribute value = “06252” Name = “Emp_ID”/>
<Attribute value = “03/06/2010” Name = “Week_Ending”/>
<Attribute value = “0034” Name = “Site_ID”/>
<Attribute value = “30” Name = “TotalHrs”/>
<Attribute value = “10” Name = “Total_Overtime”/>
</Transaction >

</Transactions>
</PDI_Approval_Message>

As you see we have common header information and we have different - different transactions.

I am writing a Store Procedure where I declared 2 temp table and I need to Insert common header record in one table, while all transactions to other table by using OpenXML.

How I can do this, any help appreciate.

Thanks,
AY

ajmal

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-21 : 14:24:30

I would look at the following helpful posts

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=140334&SearchTerms=query,xml
You also need to fix up your xml tags.


declare @xml as xml

SET @xml = '
<PDI_Approval_Message>
<header>
<SourceSystem value ="WF" Name ="Workforce"></SourceSystem>
<TransactionType value="WF_TS" Name="WF-TimeSheet" ></TransactionType>
<RequestType value ="Submit" Name="Action"></RequestType>
<SubmittedOn Value="02/06/2010" Name=""></SubmittedOn>
<SubmittedBy Value="pdi/vmuser3" Name="hannok"></SubmittedBy>
</header>
<Transactions>
<Transaction ID = " WF_b738b9b1-5cb1-48e3-98cd-044065d4ac22">
<Attributes>
<Attribute value = "0625" Name = "Emp_ID"/>
<Attribute value = "03/06/2010" Name = "Week_Ending"/>
<Attribute value = "0034" Name = "Site_ID"/>
<Attribute value = "15" Name = "TotalHrs"/>
<Attribute value = "10" Name = "Total_Overtime"/>
</Attributes>

<Attachments>
<Attachment value = "/test/file.doc" Name = "timesheetdocument"/>
<Attachment value = "/test/file1.doc" Name = "approvalletter"/>
</Attachments>
</Transaction >

<Transaction ID = " WF_728d1ec7-de8f-4575-86cb-3cbb37b133a4">
<Attribute value = "06251" Name = "Emp_ID"/>
<Attribute value = "03/06/2010" Name = "Week_Ending"/>
<Attribute value = "0034" Name = "Site_ID"/>
<Attribute value = "20" Name = "TotalHrs"/>
<Attribute value = "10" Name = "Total_Overtime"/>
</Transaction >

<Transaction ID = " WF_7ae6ec19-c677-49a1-b87c-5887d26889ba">
<Attribute value = "06252" Name = "Emp_ID"/>
<Attribute value = "03/06/2010" Name = "Week_Ending"/>
<Attribute value = "0034" Name = "Site_ID"/>
<Attribute value = "30" Name = "TotalHrs"/>
<Attribute value = "10" Name = "Total_Overtime"/>
</Transaction >

</Transactions>
</PDI_Approval_Message>'

CREATE TABLE Header (HeaderID int IDENTITY(1,1), SourceSystemValue varchar(100), SourceSystemName varchar(100), 
                      TransactionTypeValue varchar(100), TransactionTypeName varchar(100),
                      RequestTypeValue varchar(100), RequestTypeName varchar(100))
--following table variable can hold the headerid in case you need it for other table
declare @header table(HeaderID int)

INSERT INTO Header  
OUTPUT inserted.HeaderID INTO @header
                   
select a.b.value('./SourceSystem[1]/@value','varchar(100)') as SourceSystemValue,
       a.b.value('./SourceSystem[1]/@Name','varchar(100)') as SourceSystemName,
       a.b.value('./TransactionType[1]/@value','varchar(100)') as TransactionTypeValue,
       a.b.value('./TransactionType[1]/@Name','varchar(100)') as TransactionTypeName,
       a.b.value('./RequestType[1]/@value','varchar(100)') as RequestTypeValue,
       a.b.value('./RequestType[1]/@Name','varchar(100)') as RequestTypeName
FROM @xml.nodes('/PDI_Approval_Message/header')a(b)


SELECT * FROM @Header

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources