Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How To Import XML File... Expand / Collapse
Author
Message
Posted Thursday, December 06, 2012 11:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, March 15, 2014 10:37 AM
Points: 46, Visits: 182
Hi ,

I am Try to Execute the bellow Mentioned Query ...
but i am getting the only one row Result...
I want to import that XML file and also want Entire Record set...
so How to Solve the issue ...
please help me any one ASAP.....

Regards,
Saravanan.D

---------------------------------------------------------------------------


DECLARE @idoc int
DECLARE @doc xml
SET @doc ='<Root>
<Node FirstName="Andrew" LastName="Fuller" Email="afuller@contoso.com" Phone="(205) 555 - 9898" Position="CEO" Branch="TopManagement" Address="London, 120 Hanover Sq.">
<Node FirstName="Jeremy" LastName="Boather" Email="jboather@contoso.com" Phone="(205) 555 - 9888" Position="President QA" Branch="QA" Address="London, 120 Hanover Sq.">
<Node FirstName="Anne" LastName="Dodsworth" Email="adodsworth@contoso.com" Phone="(205) 555 - 9887" Position="VP QA" Branch="QA" Address="London, 120 Hanover Sq.">
<Node FirstName="Alexander" LastName="Tuckings" Email="atuckings@contoso.com" Phone="(205) 555 - 9886" Position="Team Lead Team1" Branch="QA" Address="London, 120 Hanover Sq.">
<Node FirstName="Brenda" LastName="Smith" Email="bsmith@contoso.com" Phone="(205) 555 - 9885" Position="Senior QA" Branch="QA" Address="London, 120 Hanover Sq."/>
</Node>
<Node FirstName="Mary" LastName="Bird" Email="mbird@contoso.com" Phone="(205) 555 - 9885" Position="Team Lead Team2" Branch="QA" Address="London, 120 Hanover Sq."/>
</Node>
</Node>
<Node FirstName="Steven" LastName="Buchanan" Email="sbuchanan@contoso.com" Phone="(205) 555 - 9897" Position="President Dev Dept." Branch="Development" Address="London, 120 Hanover Sq.">
<Node FirstName="Robert" LastName="King" Email="rking@contoso.com" Phone="(205) 555 - 9896" Position="VP Dev Dept." Branch="Development" Address="London, 120 Hanover Sq.">
<Node FirstName="Laura" LastName="Callahan" Email="lcallahan@contoso.com" Phone="(205) 555 - 9892" Position="Team Lead Team1" Branch="Development" Address="London, 120 Hanover Sq.">
<Node FirstName="Jason" LastName="Roland" Email="jroland@contoso.com" Phone="(205) 555 - 9872" Position="Senior Dev" Branch="Development" Address="London, 120 Hanover Sq.">
</Node>
</Node>
<Node FirstName="Eric" LastName="Danstin" Email="edanstin@contoso.com" Phone="(205) 555 - 9882" Position="Team Lead Team2" Branch="Development" Address="London, 120 Hanover Sq.">
<Node FirstName="Elizabeth" LastName="Lincoln" Email="elincoln@contoso.com" Phone="(205) 555 - 9862" Position="Senior Dev" Branch="Development" Address="London, 120 Hanover Sq.">
</Node>
<Node FirstName="Margaret" LastName="Peacock" Email="mpeacock@contoso.com" Phone="(205) 555 - 9852" Position="Senior Dev" Branch="Development" Address="London, 120 Hanover Sq.">
</Node>
</Node>
</Node>
</Node>
</Node>
</Root>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc,'/Root/Node',1)
WITH (FirstName varchar(110),
LastName varchar(210),
Email varchar(110),
Phone varchar(110),
Position varchar(110),
Branch varchar(110),
Address varchar(110))
Post #1393855
Posted Thursday, December 06, 2012 11:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 15, 2014 4:35 PM
Points: 6, Visits: 11
You need to increase the size of the variable:
DECLARE @doc varchar(1000)

It's WAY too small to hold the entire XML string...
Post #1393867
Posted Thursday, December 06, 2012 11:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, March 15, 2014 10:37 AM
Points: 46, Visits: 182
Hi Niels Christiansen,

Thank u ....
now i am using the XML Datatype

but i got a Result only one Row....
what about the other row..
How to get the Entire Record Set...
Please Help me Niels Christiansen....

Regards,
Saravanan.D
Post #1393875
Posted Friday, December 07, 2012 4:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 11:21 AM
Points: 29, Visits: 978
Hi,

is correct XML file?

in some cases the tags were not closed: <Node... /> or <Node...> </ Node> ...

DECLARE @idoc int
DECLARE @doc xml
SET @doc ='<Root>
<Node FirstName="Andrew" LastName="Fuller" Email="afuller@contoso.com" Phone="(205) 555 - 9898" Position="CEO" Branch="TopManagement" Address="London, 120 Hanover Sq.">
</Node>
<Node FirstName="Jeremy" LastName="Boather" Email="jboather@contoso.com" Phone="(205) 555 - 9888" Position="President QA" Branch="QA" Address="London, 120 Hanover Sq.">
</Node>
<Node FirstName="Anne" LastName="Dodsworth" Email="adodsworth@contoso.com" Phone="(205) 555 - 9887" Position="VP QA" Branch="QA" Address="London, 120 Hanover Sq.">
</Node>
<Node FirstName="Alexander" LastName="Tuckings" Email="atuckings@contoso.com" Phone="(205) 555 - 9886" Position="Team Lead Team1" Branch="QA" Address="London, 120 Hanover Sq.">
</Node>
<Node FirstName="Brenda" LastName="Smith" Email="bsmith@contoso.com" Phone="(205) 555 - 9885" Position="Senior QA" Branch="QA" Address="London, 120 Hanover Sq."/>
<Node FirstName="Mary" LastName="Bird" Email="mbird@contoso.com" Phone="(205) 555 - 9885" Position="Team Lead Team2" Branch="QA" Address="London, 120 Hanover Sq."/>
<Node FirstName="Steven" LastName="Buchanan" Email="sbuchanan@contoso.com" Phone="(205) 555 - 9897" Position="President Dev Dept." Branch="Development" Address="London, 120 Hanover Sq.">
</Node>
<Node FirstName="Robert" LastName="King" Email="rking@contoso.com" Phone="(205) 555 - 9896" Position="VP Dev Dept." Branch="Development" Address="London, 120 Hanover Sq.">
</Node>
<Node FirstName="Laura" LastName="Callahan" Email="lcallahan@contoso.com" Phone="(205) 555 - 9892" Position="Team Lead Team1" Branch="Development" Address="London, 120 Hanover Sq.">
</Node>
<Node FirstName="Jason" LastName="Roland" Email="jroland@contoso.com" Phone="(205) 555 - 9872" Position="Senior Dev" Branch="Development" Address="London, 120 Hanover Sq.">
</Node>
<Node FirstName="Eric" LastName="Danstin" Email="edanstin@contoso.com" Phone="(205) 555 - 9882" Position="Team Lead Team2" Branch="Development" Address="London, 120 Hanover Sq.">
</Node>
<Node FirstName="Elizabeth" LastName="Lincoln" Email="elincoln@contoso.com" Phone="(205) 555 - 9862" Position="Senior Dev" Branch="Development" Address="London, 120 Hanover Sq.">
</Node>
<Node FirstName="Margaret" LastName="Peacock" Email="mpeacock@contoso.com" Phone="(205) 555 - 9852" Position="Senior Dev" Branch="Development" Address="London, 120 Hanover Sq.">
</Node>
</Root>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc,'/Root/Node',1)
WITH (FirstName varchar(110),
LastName varchar(210),
Email varchar(110),
Phone varchar(110),
Position varchar(110),
Branch varchar(110),
Address varchar(110))

Post #1393990
Posted Friday, December 07, 2012 4:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:06 AM
Points: 2,404, Visits: 7,311
carlosaamaral (12/7/2012)
Hi,

is correct XML file?

in some cases the tags were not closed: <Node... /> or <Node...> </ Node> ...


I didn't realise that he had posted this in two places. The other thread is here --> http://www.sqlservercentral.com/Forums/FindPost1393932.aspx, where I have also stated that the XML is incorrect.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1393998
Posted Friday, December 07, 2012 10:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, March 15, 2014 10:37 AM
Points: 46, Visits: 182

I Got The Result as the Same File....




DECLARE @idoc int
DECLARE @doc xml
SET @doc ='<Root>
<Node FirstName="Andrew" LastName="Fuller" Email="afuller@contoso.com" Phone="(205) 555 - 9898" Position="CEO" Branch="TopManagement" Address="London, 120 Hanover Sq.">
<Node FirstName="Jeremy" LastName="Boather" Email="jboather@contoso.com" Phone="(205) 555 - 9888" Position="President QA" Branch="QA" Address="London, 120 Hanover Sq.">
<Node FirstName="Anne" LastName="Dodsworth" Email="adodsworth@contoso.com" Phone="(205) 555 - 9887" Position="VP QA" Branch="QA" Address="London, 120 Hanover Sq.">
<Node FirstName="Alexander" LastName="Tuckings" Email="atuckings@contoso.com" Phone="(205) 555 - 9886" Position="Team Lead Team1" Branch="QA" Address="London, 120 Hanover Sq.">
<Node FirstName="Brenda" LastName="Smith" Email="bsmith@contoso.com" Phone="(205) 555 - 9885" Position="Senior QA" Branch="QA" Address="London, 120 Hanover Sq."/>
</Node>
<Node FirstName="Mary" LastName="Bird" Email="mbird@contoso.com" Phone="(205) 555 - 9885" Position="Team Lead Team2" Branch="QA" Address="London, 120 Hanover Sq."/>
</Node>
</Node>
<Node FirstName="Steven" LastName="Buchanan" Email="sbuchanan@contoso.com" Phone="(205) 555 - 9897" Position="President Dev Dept." Branch="Development" Address="London, 120 Hanover Sq.">
<Node FirstName="Robert" LastName="King" Email="rking@contoso.com" Phone="(205) 555 - 9896" Position="VP Dev Dept." Branch="Development" Address="London, 120 Hanover Sq.">
<Node FirstName="Laura" LastName="Callahan" Email="lcallahan@contoso.com" Phone="(205) 555 - 9892" Position="Team Lead Team1" Branch="Development" Address="London, 120 Hanover Sq.">
<Node FirstName="Jason" LastName="Roland" Email="jroland@contoso.com" Phone="(205) 555 - 9872" Position="Senior Dev" Branch="Development" Address="London, 120 Hanover Sq.">
</Node>
</Node>
<Node FirstName="Eric" LastName="Danstin" Email="edanstin@contoso.com" Phone="(205) 555 - 9882" Position="Team Lead Team2" Branch="Development" Address="London, 120 Hanover Sq.">
<Node FirstName="Elizabeth" LastName="Lincoln" Email="elincoln@contoso.com" Phone="(205) 555 - 9862" Position="Senior Dev" Branch="Development" Address="London, 120 Hanover Sq.">
</Node>
<Node FirstName="Margaret" LastName="Peacock" Email="mpeacock@contoso.com" Phone="(205) 555 - 9852" Position="Senior Dev" Branch="Development" Address="London, 120 Hanover Sq.">
</Node>
</Node>
</Node>
</Node>
</Node>
</Root>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc,'//Node',1)
WITH (FirstName varchar(110),
LastName varchar(210),
Email varchar(110),
Phone varchar(110),
Position varchar(110),
Branch varchar(110),
Address varchar(110))


Regards,
K.D.Saravanan
Post #1394303
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse