Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to Insert xml data into SQL Server table?

Tags:

sql-server

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>
like image 396
Shiva Kumar Avatar asked Jan 27 '14 10:01

Shiva Kumar


People also ask

Can I import XML into SQL Server?

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.

How use XML data in SQL Server?

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.

How insert bulk data in SQL Server using XML?

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.

How do I get data from XML format in SQL Server?

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.


2 Answers

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)
like image 176
marc_s Avatar answered Nov 16 '22 01:11

marc_s


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';
like image 42
kavitha Reddy Avatar answered Nov 16 '22 00:11

kavitha Reddy