xml file insert into sql server table using tsql query 

DECLARE @xml XML

SELECT @xml = x.y
FROM OPENROWSET( BULK 'c:\temp\temp.xml', SINGLE_CLOB ) x(y)


--INSERT INTO yourTable ...
SELECT
 x.y.value('@Address', 'VARCHAR(MAX)') AS [Address],
 x.y.value('@Branch', 'VARCHAR(MAX)') AS Branch,
 x.y.value('@Position', 'VARCHAR(MAX)') AS Position,
 x.y.value('@Phone', 'VARCHAR(MAX)') AS Phone,
 x.y.value('@Email', 'VARCHAR(MAX)') AS Email,
 x.y.value('@LastName', 'VARCHAR(MAX)') AS LastName,
 x.y.value('@FirstName', 'VARCHAR(MAX)') AS FirstName
FROM @xml.nodes('//Node') AS x(y)
.................................................................................................................................................................
DECLARE @X XML ='<?xml version="1.0" encoding="UTF-8"?>
<Root>

<Node Address="London, 120 Hanover Sq." Branch="TopManagement" Position="CEO" Phone="(205) 555 - 9898" Email="afuller@contoso.com" LastName="Fuller" FirstName="Andrew">
</Node>

<Node Address="London, 120 Hanover Sq." Branch="QA" Position="President QA" Phone="(205) 555 - 9888" Email="jboather@contoso.com" LastName="Boather" FirstName="Jeremy">
</Node>

<Node Address="London, 120 Hanover Sq." Branch="QA" Position="VP QA" Phone="(205) 555 - 9887" Email="adodsworth@contoso.com" LastName="Dodsworth" FirstName="Anne">
</Node>

<Node Address="London, 120 Hanover Sq." Branch="QA" Position="Team Lead Team1" Phone="(205) 555 - 9886" Email="atuckings@contoso.com" LastName="Tuckings" FirstName="Alexander">
</Node>

<Node Address="London, 120 Hanover Sq." Branch="QA" Position="Senior QA" Phone="(205) 555 - 9885" Email="bsmith@contoso.com" LastName="Smith" FirstName="Brenda">
</Node>

<Node Address="London, 120 Hanover Sq." Branch="QA" Position="Team Lead Team2" Phone="(205) 555 - 9885" Email="mbird@contoso.com" LastName="Bird" FirstName="Mary">
</Node>

<Node Address="London, 120 Hanover Sq." Branch="Development" Position="President Dev Dept." Phone="(205) 555 - 9897" Email="sbuchanan@contoso.com" LastName="Buchanan" FirstName="Steven">
</Node>

<Node Address="London, 120 Hanover Sq." Branch="Development" Position="VP Dev Dept." Phone="(205) 555 - 9896" Email="rking@contoso.com" LastName="King" FirstName="Robert">
</Node>

<Node Address="London, 120 Hanover Sq." Branch="Development" Position="Team Lead Team1" Phone="(205) 555 - 9892" Email="lcallahan@contoso.com" LastName="Callahan" FirstName="Laura">
</Node>

<Node Address="London, 120 Hanover Sq." Branch="Development" Position="Senior Dev" Phone="(205) 555 - 9872" Email="jroland@contoso.com" LastName="Roland" FirstName="Jason">
</Node>

<Node Address="London, 120 Hanover Sq." Branch="Development" Position="Team Lead Team2" Phone="(205) 555 - 9882" Email="edanstin@contoso.com" LastName="Danstin" FirstName="Eric">
</Node>

<Node Address="London, 120 Hanover Sq." Branch="Development" Position="Senior Dev" Phone="(205) 555 - 9862" Email="elincoln@contoso.com" LastName="Lincoln" FirstName="Elizabeth">
</Node>

<Node Address="London, 120 Hanover Sq." Branch="Development" Position="Senior Dev" Phone="(205) 555 - 9852" Email="mpeacock@contoso.com" LastName="Peacock" FirstName="Margaret">
</Node>

</Root>'

INSERT INTO YRTABLENAME ([Address],Branch,Position,Phone,Email,LastName,FirstName)

SELECT
n.i.value('@Address','varchar(20)') AS [Address],
n.i.value('@Branch','varchar(20)') AS Branch,
n.i.value('@Position','varchar(20)') AS Position,
n.i.value('@Phone','varchar(20)') AS Phone,
n.i.value('@Email','varchar(20)') as Email,
n.i.value('@LastName','varchar(20)') AS LastName,
n.i.value('@FirstName','varchar(20)') AS FirstName
FROM @X.nodes('Root') AS x(I)
CROSS APPLY
x.I.nodes('Node') AS n(i)

Comments

Post a Comment

Popular posts from this blog

MOLAP, ROLAP, And HOLAP

In the OLAP world, there are mainly two different types: Multidimensional OLAP (MOLAP) and Relational OLAP (ROLAP). Hybrid OLAP (HOLAP) refers to technologies that combine MOLAP and ROLAP. MOLAP This is the more traditional way of OLAP analysis. In MOLAP, data is stored in a multidimensional cube. The storage is not in the relational database, but in proprietary formats. Advantages : Excellent performance: MOLAP cubes are built for fast data retrieval, and is optimal for slicing and dicing operations. Can perform complex calculations: All calculations have been pre-generated when the cube is created. Hence, complex calculations are not only doable, but they return quickly. Disadvantages : Limited in the amount of data it can handle: Because all calculations are performed when the cube is built, it is not possible to include a large amount of data in the cube itself. This is not to say that the data in the cube cannot be derived from a large amount of data. Indeed, this...
Read more

Difference Between CURRENT_TIMESTAMP and GETDATE()

Image
SQL SERVER – Difference Between CURRENT_TIMESTAMP and GETDATE() – CURRENT_TIMESTAMP Equivalent in SQL Server January 4, 2012 by  s uraj A common question – I often get from Oracle/MySQL Professionals: “What is the Equivalent to CURRENT_TIMESTAMP in SQL Server?” Here is a common question I often get from SQL Server Professionals: “What are differences between Difference Between CURRENT_TIMESTAMP and GETDATE ()?” Very simple question but have showed up so frequently that I feel like to write about it. Well in SQL Server GETDATE() is Equivalent to CURRENT_TIMESTAMP. However, if you use CURRENT_TIMESTAMP in your select statement it will work fine. You can see in the above example – both of them returns the same value. Now let us go to next question regarding difference between GETDATE and CURRENT_TIMESTAMP. Well, the matter of the fact, there is no difference between them in SQL Server ( Reference Link ). CURRENT_TIMESTAMP is an ANS...
Read more

Convert Seconds to HH:MM:SS

Convert Seconds to HH:MM:SS Today 2 of my colleagues from reporting team had this requirement. They had one table where Seconds were stored as INT and in report they wanted to convert the seconds to HH:MM:SS format.  They already had  1  solution ready with them.   It was something like: --------------------------------------------------------------------------------------------------------------- DECLARE  @Seconds  INT SET  @Seconds = 3800 SELECT   CONVERT ( VARCHAR (10),@Seconds/3600)     + ':'     + RIGHT( '00' + CONVERT ( VARCHAR (2),(@Seconds%3600)/60),2)     + ':'     + RIGHT( '00' + CONVERT ( VARCHAR (2),@Seconds%60),2)  AS  [HH:MM:SS]    GO --------------------------------------------------------------------------------------------------------------- -- The output--  01:03:20 It was working fine, but my colleagues were looking for som...
Read more