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 12»»

Reading from XML does not work Expand / Collapse
Author
Message
Posted Thursday, April 30, 2009 4:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 8, 2010 9:26 AM
Points: 8, Visits: 13


ALTER Procedure [dbo].[TestOpenXML]

@strXML ntext
AS
Declare @xmlDat xml,

@xmlPointer int

SELECT @strXML = N''

exec sp_xml_preparedocument @xmlPointer OUTPUT, @strXML

INSERT INTO Employee(EmployeeName, EmployeeSalary) Select eName, eSalary From OpenXML(@xmlPointer,'/record/deptid/Employee',2) WITH (eName varchar(200) '@Name', eSalary real '@Salary')

Go
Post #707540
Posted Thursday, April 30, 2009 4:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:59 AM
Points: 6,823, Visits: 13,268
Instead of using OpenXML you should look into XQuery.
If you have trouble getting XQuery to work, please attach the XML file together with what you've tried and we probably can help you getting it to work.

Edit: Regarding your code in prev. post: With the SELECT @strXML = N'' you set your xml document to an empty string and your variable @xmlDat is not used at all...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #707549
Posted Thursday, April 30, 2009 4:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Hi

Your XML is not valid.

* XML is case sensitive
* Start element "record" and end element "RECORD"
* Start element "deptid" and end element "DEPT"
* Start element "DEPTID" and end element "DEPT"

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #707554
Posted Thursday, April 30, 2009 5:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:59 AM
Points: 6,823, Visits: 13,268
Florian Reischl (4/30/2009)
Hi

Your XML is not valid.

* XML is case sensitive
* Start element "record" and end element "RECORD"
* Start element "deptid" and end element "DEPT"
* Start element "DEPTID" and end element "DEPT"

Greets
Flo


Hi Flo,

were do you see the XML code? Attached a screenshot of the post as it's shown on my scrren (Firefox 2.0.0.9)...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function


  Post Attachments 
screeenshot_post707540.jpg (3 views, 195.92 KB)
Post #707562
Posted Thursday, April 30, 2009 5:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Hey Lutz!

Install Firefox 3.0.10, then err... click "Quote" at his post

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #707572
Posted Thursday, April 30, 2009 5:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 8, 2010 9:26 AM
Points: 8, Visits: 13
Spotted that and corrected the xml. Here it is:
''
Post #707579
Posted Thursday, April 30, 2009 5:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 8, 2010 9:26 AM
Points: 8, Visits: 13
''
Post #707580
Posted Thursday, April 30, 2009 5:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 8, 2010 9:26 AM
Points: 8, Visits: 13
''
Post #707581
Posted Thursday, April 30, 2009 5:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 8, 2010 9:26 AM
Points: 8, Visits: 13
Any good resources of starting with XQuery
Post #707582
Posted Thursday, April 30, 2009 5:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Hi kavitus

Still not valid:
<DEPTID = "10">
... is missing the attribute qualifier.

Tip for posting XML the best way is just attach as file.

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #707584
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse