How to import below XML data into SQL Server table with three columns?
如何将下面的XML数据导入到具有三列的SQL Server表中?
<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>
2 个解决方案
#1
24
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)
#2
3
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 @xmlData.nodes('/ Customers / customer') as [Table]([Column])
IF(@@ROWCOUNT > 0 )
SET @retValue='SUCCESS';
#1
24
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)
#2
3
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 @xmlData.nodes('/ Customers / customer') as [Table]([Column])
IF(@@ROWCOUNT > 0 )
SET @retValue='SUCCESS';