Menu
How to import below XML data into SQL Server table with three columns?
<dataset>
<metadata>
<item name="NAME_LAST" type="xs:string" length="62" />
<item name="NAME_FIRST" type="xs:string" length="62" />
<item name="NAME_MIDDLE" type="xs:string" length="32" />
</metadata>
<data>
<row>
<value>SMITH</value>
<value>MARY</value>
<value>N</value>
</row>
<row>
<value>SMITH2</value>
<value>MARY2</value>
<value>N2</value>
</row>
</data>
</dataset>
396
You can transfer XML data into SQL Server in several ways. For example: If you have your data in an [n]text or image column in a SQL Server database, you can import the table by using Integration Services. Change the column type to XML by using the ALTER TABLE statement.
To create a SQL table using XML elements, all you have to do is to change the mode value of the OPENXML function to 2 and change the name of the attributes to the name of the element you want to retrieve.
When you bulk import XML data from a file that contains an encoding declaration that you want to apply, specify the SINGLE_BLOB option in the OPENROWSET(BULK...) clause. The SINGLE_BLOB option ensures that the XML parser in SQL Server imports the data according to the encoding scheme specified in the XML declaration.
SQL Server lets you retrieve data as XML by supporting the FOR XML clause, which can be included as part of your query. You can use the FOR XML clause in the main (outer) query as well as in subqueries. The clause supports numerous options that let you define the format of the XML data.
Try this:
DECLARE @input XML = '<dataset>
<metadata>
<item name="NAME_LAST" type="xs:string" length="62" />
<item name="NAME_FIRST" type="xs:string" length="62" />
<item name="NAME_MIDDLE" type="xs:string" length="32" />
</metadata>
<data>
<row>
<value>SMITH</value>
<value>MARY</value>
<value>N</value>
</row>
<row>
<value>SMITH2</value>
<value>MARY2</value>
<value>N2</value>
</row>
</data>
</dataset>'
INSERT INTO dbo.YourTable(ColName, ColFirstName, ColOther)
SELECT
Name = XCol.value('(value)[1]','varchar(25)'),
FirstName = XCol.value('(value)[2]','varchar(25)'),
OtherValue = XCol.value('(value)[3]','varchar(25)')
FROM
@input.nodes('/dataset/data/row') AS XTbl(XCol)
Insert XML Data into sql Server table
Declare @retValue1 varchar(50);
Declare @XmlStr XML;
SET @XmlStr='<Customers>
<customer>
<ID>111589</ID>
<FirstName>name1</FirstName>
<LastName>Lname1</LastName>
<Company>ABC</Company>
</customer>
<customer>
<ID>12345</ID>
<FirstName>name2</FirstName>
<LastName>Lname2</LastName>
<Company>ABC</Company>
</customer>
<customer>
<ID>14567</ID>
<FirstName>name3</FirstName>
<LastName>Lname3</LastName>
<Company>DEF</Company>
</customer>
</Customers>';
@retValue='Failed';
INSERT INTO [test_xmlinsert](
[id],
[firstName],
[lastName],
[company]
)
SELECT
COALESCE([Table].[Column].value('ID[1]', 'int'),0) as 'ID',
[Table].[Column].value('FirstName [1]', 'varchar(50)') as ' FirstName ',
[Table].[Column].value(' LastName[1]', 'varchar(50)') as ' LastName',
[Table].[Column].value(' Company [1]', 'varchar(50)') as ' Company'
FROM @XmlStr.nodes('/ Customers / customer') as [Table]([Column])
IF(@@ROWCOUNT > 0 )
SET @retValue='SUCCESS';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
