I receive XML data like
<t>
<ID>8</ID>
<FirstName>name 8</FirstName>
<LastName>surname 8</LastName>
<DateOfBirth>1963-05-23T00:00:00</DateOfBirth>
</t>
In a SQL Server stored procedure. There are different xml data formats (the field numbers and field names can differ). Now, I should extract each field and it value and insert each field/value as a separate record in a table.
Are there examples how I can do this with TSQL?
You can easily scan the XML - if it's always under a <t> root node and always just one level deep:
DECLARE @input XML = '<t>
<ID>8</ID>
<FirstName>name 8</FirstName>
<LastName>surname 8</LastName>
<DateOfBirth>1963-05-23T00:00:00</DateOfBirth>
</t>'
SELECT
FieldName = nodes.value('local-name(.)', 'varchar(50)'),
FieldValue = nodes.value('(.)[1]', 'varchar(50)')
FROM
@input.nodes('/t/*') AS Tbl(nodes)
Output is:
But it really depends on what you want to do with that data now - once you've retrieved it from the XML .... what does the structure of your intended target table look like?
Where @x is your xml
insert yourtable (ID, Firstname, Lastname, DOB)
select
@x.value('(/t/ID)[1]','int'),
@x.value('(/t/FirstName)[1]','varchar(50)'),
@x.value('(/t/LastName)[1]','varchar(50)'),
@x.value('(/t/DateOfBirth)[1]','datetime')
See http://msdn.microsoft.com/en-us/library/ms178030(v=sql.100).aspx
