April 30, 2009 at 4:34 am
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
April 30, 2009 at 4:46 am
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...
April 30, 2009 at 4:55 am
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
April 30, 2009 at 5:06 am
Florian Reischl (4/30/2009)
HiYour 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)... :doze:
April 30, 2009 at 5:14 am
Hey Lutz!
Install Firefox 3.0.10, then err... click "Quote" at his post 😛
Greets
Flo
April 30, 2009 at 5:29 am
Spotted that and corrected the xml. Here it is:
''
April 30, 2009 at 5:30 am
''
April 30, 2009 at 5:30 am
''
April 30, 2009 at 5:34 am
Any good resources of starting with XQuery
April 30, 2009 at 5:39 am
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
April 30, 2009 at 5:45 am
kavitus (4/30/2009)
Any good resources of starting with XQuery
I found the following article very helpful:
http://www.simple-talk.com/sql/t-sql-programming/xml-jumpstart-workbench/
@Flo:
The "quote-trick" works on my FireFox-Version, too... :w00t:
April 30, 2009 at 5:51 am
kavitus (4/30/2009)
Any good resources of starting with XQuery
There are some good example in BOL.
Here a little sample for your XML. Do not copy the rendered code! It is damaged by the render engine of this forum... Use the "Quote" button for this post and copy the source from there.
DECLARE @strXML NVARCHAR(MAX)
SELECT @strXML = N'
'
Declare @xmlDat xml
SELECT @xmlDat = @strXML
SELECT
T.C.value('../@id', 'int') DeptId,
T.C.value('@Name', 'varchar(30)') Name,
T.C.value('@Salary', 'int') Salary
FROM @xmlDat.nodes('RECORD/DEPTID/Employee') T(C)
Greets
Flo
April 30, 2009 at 5:54 am
lmu92 (4/30/2009)
@Flo:The "quote-trick" works on my FireFox-Version, too... :w00t:
Just kidding ;-). The quote-trick works on every browser. The problem is not the browser but the java-script client-side render engine...
Sorry
Flo
May 1, 2009 at 2:40 pm
To make your XML show up, you need to qoute it with [ code="xml"]...[/code] tags, instead of '...'. Like this:
kavitus (4/30/2009)
Click the QUOTE button on my post to see how I did this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 5, 2009 at 5:20 am
Thanks guys. I guess the problem was with the XML not being valid.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply