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)
 Inserting Records to table from XML parameter

Author  Topic 

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2012-07-03 : 07:48:25
I am new to using XML in SQL statements. I have a stored procedure that receives xml parameter (@InputXML xml). I need to insert values to table @MyTable. How can write this SQL statement for this xml in the most efficient way?

Stored Procedure

CREATE PROCEDURE ParseXML (@InputXML xml)
AS
BEGIN

DECLARE @MyTable TABLE (BankAccountID int, StatusVal varchar(max))



END
GO


XML File

<?xml version="1.0" encoding="utf-16"?>

<ArrayOfBankAccountDTOForStatus
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<BankAccountDTOForStatus>
<BankAccountID>2</BankAccountID>
<Status>FrozenFA</Status>
</BankAccountDTOForStatus>

<BankAccountDTOForStatus>
<BankAccountID>3</BankAccountID>
<Status>FrozenSB</Status>
</BankAccountDTOForStatus>

</ArrayOfBankAccountDTOForStatus>

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-03 : 13:48:34
[code]INSERT INTO @MyTable
(BankAccountID,STATUS)
SELECT
c.value('BankAccountID[1]','int'),
c.value('Status[1]','varchar(32)')
FROM
@inputXML.nodes('//BankAccountDTOForStatus') T(c);[/code]
Go to Top of Page
   

- Advertisement -