Inserting Records to table from XML parameter

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]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources