July 24, 2009 at 2:04 am
Please help!!!
I have an XML file of the format
I need to parse this into a table structure. I have used OPENXML with namespace option to parse it. It works fine if I remove the offending soapenv tags but fails wghen I include them. Please help me in parsing this XML.
The code I have used is
DECLARE @XmlDocumentHandle int
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XMLDoc, '
SELECT
*
FROM
OPENXML (@XmlDocumentHandle, '/a:getActiveMeetingsResponse/a:return', 2)
WITH(
issuercode varchar(10) 'a:issuerCode' ,
meetingId varchar(12) 'a:meetingId'
)
July 24, 2009 at 1:03 pm
A few thoughts:
1) you should think about using XQuery instead of OpenXML.
2) you need to qualify every name space you're using in your xml.
Please see the following example:
DECLARE @xml XML
SET @xml='' -- insert your xml
;WITH XMLNAMESPACES('abc.com' AS soapenv,
DEFAULT 'abcd.com')
SELECT c.value('issuerCode[1]','Varchar(30)') AS issuerCode
FROM @xml.nodes ('soapenv:Envelope/soapenv:Body/getActiveMeetingsResponse/return')AS T(c)
/* result set
issuerCode
HFAU
*/
Based on this code snippet it should be easy to extend it to meet your requirements.
July 26, 2009 at 7:56 pm
Thank You for the response. This was a sample XML I will try with the actual XML and check how it works. In the meantime with your code snippet I have found a way of achieving the same using OPENXML as well
DECLARE @XmlDocumentHandle int
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XMLDoc, ''
SELECT
*
FROM
OPENXML (@XmlDocumentHandle, '/soapenv:Envelope/soapenv:Body/a:getActiveMeetingsResponse/a:return', 2)
WITH(
issuercode varchar(10) 'a:issuerCode' ,
meetingId varchar(12) 'a:meetingId'
)
EXEC sp_xml_removedocument @XmlDocumentHandle
Just being inquisitive on this issue, Why is better to use XQuery instead of OpenXML. I have read of couple of blogs related to performance gains of using XQuery Over OPENXML and did not find anything concrete.
July 27, 2009 at 12:48 pm
three links I'd recommend to look into:
http://www.sqlservercentral.com/Forums/FindPost417131.aspx, and
http://articles.techrepublic.com.com/5100-10878_11-6140404.html
I, personally, moved from OpenXML to XQuery for the following reason:
I can store xml data as xml type in the database (if I have to), and still be able to query or modify data almost like relational data.
To my best knowledge there is no way to use OpenXML for direct data manipulation. In order to avoid different type of handling XML data for loading, query and modification I switched to XQuery.
July 27, 2009 at 6:12 pm
Thanks for your inputs. All these days I have used OPENXML without worrying about the performance implications.
July 28, 2009 at 2:09 am
As this is the first time I am working with XML with namespaces I am having a lot of issues parsing the data.
yet again I have run into problems with my result set. I am getting the following error:
Error converting data type nvarchar to numeric.
I know that the tag is a numeric value. If i convert the data type to varchar it works fine, but I was wondering what am I doing wrong here?
Listed below is the query I have used.
Please check my sample code
declare @XMLDoc XML
SET @XMLDoc =
'
FAC
0
FACEG200901
2550590.4800
1
5000
5000
0
0
0
0
0
0
100.00
0
0
5000
1
0
0
5000
1
1
00001
PSH
0
PSHGM200901
1
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
00001
'
--Resolution Votes
;WITH XMLNAMESPACES('abc.com' AS soapenv, 'abcd.com' AS a, 'abcde.com' AS xsi )
SELECT
c.value('../../a:issuerCode[1]','Varchar(10)') AS IssuerCode,
c.value('../a:meetingId[1]', 'Varchar(12)') AS MeetingID,
c.value('../a:issuedCapital[1]', 'INT') AS IssuedCapital,
c.value('../a:totalValidForms[1]', 'INT') AS TotalValidForms,
c.value('a:resolutionNbr[1]', 'INT') AS ResolutionID,
c.value('xsi:availVotesPerIssuedCapital[1]', 'NUMERIC(20,6)') AS AvailVotesPerIssuedCapital,
c.value('a:totalDisregardedVotes[1]', 'INT') AS TotalDisregardedVotes,
c.value('xsi:votesForPercAvailVotes[1]', 'NUMERIC(20,6)') AS VotesForPercAvailVotes
FROM
@XMLDoc.nodes ('soapenv:Envelope/soapenv:Body/a:getActiveMeetingSummaryDataResponse/a:return/a:meeting/a:resolution')AS T(c)
--Resolution Votes
;WITH XMLNAMESPACES('abc.com' AS soapenv, 'abcd.com' AS a, 'abcde.com' AS xsi )
SELECT
c.value('../../a:issuerCode[1]','Varchar(10)') AS IssuerCode,
c.value('../a:meetingId[1]', 'Varchar(12)') AS MeetingID,
c.value('../a:issuedCapital[1]', 'INT') AS IssuedCapital,
c.value('../a:totalValidForms[1]', 'INT') AS TotalValidForms,
c.value('a:resolutionNbr[1]', 'INT') AS ResolutionID,
c.value('a:availVotesPerIssuedCapital[1]', 'NUMERIC(20,6)') AS AvailVotesPerIssuedCapital,
c.value('a:totalDisregardedVotes[1]', 'INT') AS TotalDisregardedVotes,
c.value('a:votesForPercAvailVotes[1]', 'NUMERIC(20,6)') AS VotesForPercAvailVotes
FROM
@XMLDoc.nodes ('soapenv:Envelope/soapenv:Body/a:getActiveMeetingSummaryDataResponse/a:return/a:meeting/a:resolution')AS T(c)
--totalDisregardedVotes
July 28, 2009 at 9:46 am
APARNA (7/27/2009)
Thanks for your inputs. All these days I have used OPENXML without worrying about the performance implications.
You should worry! See: http://www.sqlservercentral.com/blogs/michael_coles/archive/2008/01/20/stop-using-openxml-please.aspx
July 28, 2009 at 12:59 pm
Hi aparna,
the problem is within your xml data:
availVotesPerIssuedCapital xsi:nil="true" xmlns:xsi="abcde.com"
votesAgainstPercAvailVotes xsi:nil="true" xmlns:xsi="abcde.com"
votesOpenPercAvailVotes xsi:nil="true" xmlns:xsi="abcde.com"
votesForPercAvailVotes xsi:nil="true" xmlns:xsi="abcde.com"
Those elements are part of a typed element structure, which normally is used with a schema bind to it.
Within the schema, the element need to have the definition nillable="true" to indicate that NULL values are valid.
I assume you don't have a schema "abcde.com" declared within your SQL server. Therefore, the server cannot convert xsi:nil="true" to NULL and returns an empty string that cannot be converted to a numeric value.
I'd recommed to either add a schema collection for "abcde.com" or to query the data as varchar and do a case based conversion:
;WITH XMLNAMESPACES('abc.com' AS soapenv, 'abcd.com' AS a, 'abcde.com' AS xsi )
SELECT
c.value('a:issuerCode[1]','Varchar(10)') AS IssuerCode,
c2.value('a:meetingId[1]', 'Varchar(12)') AS MeetingID,
c2.value('a:issuedCapital[1]', 'INT') AS IssuedCapital,
c2.value('a:totalValidForms[1]', 'INT') AS TotalValidForms,
c3.value('a:resolutionNbr[1]', 'INT') AS ResolutionID,
CASE WHEN LEN(c3.value('a:availVotesPerIssuedCapital[1]', 'Varchar(20)'))=0 THEN 0.00 ELSE c3.value('a:availVotesPerIssuedCapital[1]', 'Numeric(20,5)') END AS AvailVotesPerIssuedCapital,
c3.value('a:totalDisregardedVotes[1]', 'INT') AS TotalDisregardedVotes,
CASE WHEN LEN(c3.value('a:votesForPercAvailVotes[1]', 'Varchar(20)'))=0 THEN 0.00 ELSE c3.value('a:votesForPercAvailVotes[1]', 'Numeric(20,5)') END AS VotesForPercAvailVotes
FROM
@XMLDoc.nodes ('soapenv:Envelope/soapenv:Body/a:getActiveMeetingSummaryDataResponse/a:return') AS T(c)
CROSS APPLY T.c.nodes ('a:meeting') AS T2(c2)
CROSS APPLY T2.c2.nodes ('a:resolution')AS T3(c3)
/* result set
IssuerCodeMeetingIDIssuedCapitalTotalValidFormsResolutionIDAvailVotesPerIssuedCapitalTotalDisregardedVotesVotesForPercAvailVotes
FACFACEG2009010112550590.480005000100.00000
PSHPSHGM2009010NULL10.0000000.00000
*/
Also, please note that I used CROSS APPLY to query the parent nodes. Based on the current execution plan it's more than 6 times faster than the "../" Version when running your sample data (another reason why I like OpenXML )
July 28, 2009 at 7:15 pm
Thanks a Ton. The case statement proved very useful and the CROSS APPLY as well.
You are right I do not have namespaces as "abc.com", "abcd.com" , "abcde.com"
They are repectively
http://www.w3.org/2003/05/soap-envelope, abcd has company specific details of the vendor providing the web service and abcde.com is http://www.w3.org/2001/XMLSchema-instance
If it is a standard from w3org should it not recognize that attribute xsi:nil="true" as NULL and therefor not give me the character conversion error.
Your Comments:
Also, please note that I used CROSS APPLY to query the parent nodes. Based on the current execution plan it's more than 6 times faster than the "../" Version when running your sample data (another reason why I like OpenXML )
Where is OPENXML used here?
July 29, 2009 at 1:00 pm
APARNA (7/28/2009)
Thanks a Ton. The case statement proved very useful and the CROSS APPLY as well.
You're very welcome!
If it is a standard from w3org should it not recognize that attribute xsi:nil="true" as NULL and therefor not give me the character conversion error.
As far as I understand the schema needs to specify whether xsi:nil is allowed for an element or not. But I'm not 100% sure. Maybe some of the xml gurus around can clarify.
Your Comments:
Also, please note that I used CROSS APPLY to query the parent nodes. Based on the current execution plan it's more than 6 times faster than the "../" Version when running your sample data (another reason why I like OpenXML )
Where is OPENXML used here?
You're right, OPENXML is not used in the sample code. I just tried to point out that XQuery has different ways on how to deal with a scenario. It's the variety of options I like.
August 2, 2009 at 6:24 pm
Thanks lutz,
You have been very helpful all through my issues with parsing XML. But I have run into a problem that I have posted in the TSQL forum as well but unfortunately had no replies yet. Thought you might help me with this issue as well.
Apostrophe causing issue in parsing XML #762918
declare @xml xml
set @xml =
'
test1 '
'
select c.value('issuerCode[1]', 'varchar(20)') AS test
from
@xml.nodes('/root/issuers') AS T(c)
Now instead of ' inside the text if I have ' it breaks.
As ' is not a mandatory encoding character as per w3 standards the vendor who is generating the XML does not want to encode it. Is it a SQL issue or issue with my understanding?[/quote]
August 2, 2009 at 6:30 pm
You have been very helpful all through my issues with parsing XML. But I have run into a problem that I have posted in the TSQL forum as well but unfortunately had no replies yet. Thought you might help me with this issue as well.
Apostrophe causing issue in parsing XML #762918
declare @xml xml
set @xml =
'
test1 '
'
select c.value('issuerCode[1]', 'varchar(20)') AS test
from
@xml.nodes('/root/issuers') AS T(c)
Now instead of ' inside the text if I have ' it breaks.
As ' is not a mandatory encoding character as per w3 standards the vendor who is generating the XML does not want to encode it. Is it a SQL issue or issue with my understanding?
August 2, 2009 at 9:13 pm
Heh... and I thought XML was supposed to make stuff easy. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2009 at 12:31 am
As this is my first experience with XML I am finding it difficult and seeking the advice of the SQL & XML gurus. Can you please point me in the right direction for better way of asking questions and getting answered. Please let me know if I am not following any of the SQL standards. Sorry If I was not clear enough in asking the right questions!
Aparna
August 3, 2009 at 11:26 pm
I have figured that this is an issue only when we query from management studio.
It is not an issue when querying using the xquery for a table having an xml field.
For instance:
create table
#temp
(
xml xml
)
insert into #temp
(xml)
values
(
'
oran''ge
'
)
select
xml.value('(/root/fruits/name)[1]', 'varchar(10)')
from
#temp
drop table #temp
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply