• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 839
  • Last Modified:

OpenXML with namespace and schema

I have tried everything I know to get this to work.  How whould I use openxml considering the schema and namespaces involved?

<?xml version="1.0" encoding="utf-8"?>
<DataSet xmlns="https://va.eftsecure.net/web_services/vterm_extensions/transaction_processing">
            <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
            <xs:element name="NewDataSet" msdata:IsDataSet="true">
            <xs:complexType>
            <xs:choice maxOccurs="unbounded">
            <xs:element name="Table1">
            <xs:complexType>
            <xs:sequence>
            <xs:element name="APPROVAL_INDICATOR" type="xs:string" minOccurs="0" />
            <xs:element name="CODE" type="xs:string" minOccurs="0" />
            <xs:element name="MESSAGE" type="xs:string" minOccurs="0" />
            <xs:element name="FRONT_END_INDICATOR" type="xs:string" minOccurs="0" />
            <xs:element name="CVV_INDICATOR" type="xs:string" minOccurs="0" />
            <xs:element name="AVS_INDICATOR" type="xs:string" minOccurs="0" />
            <xs:element name="RISK_INDICATOR" type="xs:string" minOccurs="0" />
            <xs:element name="REFERENCE" type="xs:string" minOccurs="0" />
            <xs:element name="ORDER_NUMBER" type="xs:string" minOccurs="0" />
            </xs:sequence>
            </xs:complexType>
            </xs:element>
            </xs:choice>
            </xs:complexType>
            </xs:element>
            </xs:schema>
      <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
            <NewDataSet xmlns="">
                  <Table1 diffgr:id="Table11" msdata:rowOrder="0" diffgr:hasChanges="inserted">
                        <APPROVAL_INDICATOR>X</APPROVAL_INDICATOR>
                        <CODE>000001</CODE>
                        <MESSAGE>DUPLICATE - APPROVED 000001     </MESSAGE>
                        <FRONT_END_INDICATOR>13</FRONT_END_INDICATOR>
                        <CVV_INDICATOR>M</CVV_INDICATOR>
                        <AVS_INDICATOR> </AVS_INDICATOR>
                        <RISK_INDICATOR>00</RISK_INDICATOR>
                        <REFERENCE>Ac5e29f40e</REFERENCE>
                        <ORDER_NUMBER>11111</ORDER_NUMBER>
                  </Table1>
            </NewDataSet>
      </diffgr:diffgram>
</DataSet>
0
awiinc
Asked:
awiinc
  • 7
  • 5
2 Solutions
 
YZlatCommented:
try

SELECT APPROVAL_INDICATOR,CODE,MESSAGE, .....
FROM OPENXML (@hdoc, '/NewDataSet/Table1',1)
0
 
awiincAuthor Commented:
YZlat,
Thanks for the quick response, but this returns nothing.  The namespaces, I believe are making this a bit more extraordinary.
0
 
Anthony PerkinsCommented:
You need to include the namespace in the sp_xml_preparedocument command, as well as modify the OPENXML appropriately.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
awiincAuthor Commented:
acperkins,

That is the question I am asking.  What would be the syntax of including the namespaces.  I have tried numerous namespace entries in the sp_xml_preparedocument such as

EXEC sp_xml_preparedocument @idoc OUTPUT, @retval2, '<root xmlns:n="https://va.eftsecure.net/web_services/vterm_extensions/transaction_processing" />'

for some reason I can't get the exact namespace / OPENXML stuff to return data from TABLE1
0
 
Anthony PerkinsCommented:
Post your OPENXML as it stands now.
0
 
awiincAuthor Commented:
EXEC sp_xml_preparedocument @idoc OUTPUT, @retval2, '<root xmlns:n="https://va.eftsecure.net/web_services/vterm_extensions/transaction_processing" />'


SELECT *
      FROM   OPENXML (@idoc, '/n:DataSet/n:NewDataSet/n:Table1',3)
      WITH
      (
      [Code] [varchar] (1)       'n:CODE'
      )
      
      EXEC sp_xml_removedocument @idoc
0
 
awiincAuthor Commented:
I think the part that might be throwing me the most is "<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">".  Is this a namespace that I need to use somewhere?
0
 
awiincAuthor Commented:
I got it!

SELECT *
FROM OPENXML (@idoc, '//NewDataSet/Table1',2)
      with
      (
      [Code] [varchar] (10) 'CODE'
      )
EXEC sp_xml_removedocument @idoc

found at http://www.xmlpitstop.com/NewsGroups/readpost.aspx?MessageID=%3c1AF8E83C-0353-4A88-AAF0-B30D4AE41123%40microsoft.com%3e&NewsGroup=microsoft.public.sqlserver.xml&GroupID=12

0
 
awiincAuthor Commented:
Although none of the answers were correct, I appreciate your help and quick responses.
0
 
Anthony PerkinsCommented:
This is the correct answer (it is never a good idea to rely on global XPath):

EXEC sp_xml_preparedocument @idoc OUTPUT, @retval2, '<root xmlns:n="https://va.eftsecure.net/web_services/vterm_extensions/transaction_processing"
                                                xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1"/>'
SELECT *
     FROM   OPENXML (@idoc, 'n:DataSet/diffgr:diffgram/NewDataSet/Table1', 3) WITH (
            Code varchar(10) 'CODE')
     
EXEC sp_xml_removedocument @idoc

And let me remind you to re-read the EE Guidelines regarding grading standards at:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi73

A grade of "C" is clearly not in order here in this case.
0
 
Anthony PerkinsCommented:
Any comments?
0
 
awiincAuthor Commented:
Mr. Moderator,

Could you change the grade to a A for Mr. acperkins and YZlat.  I missed the boat by giving a "C"...I didn't understand the grading level of "C" being "vendictive".  But at the same time...the answers that were presented to me before I figured it out myself, as I say in one of my comments, were not the solution -- C being average.  Since I graded before Mr. acperkins gave me his "CORRECT answer, it is my sincerest appologies to Mr. acperkins and anyone else I may have offended.  I promise to never let this happen again.  So plezzzzzzzz Mr. Moderator change the grade to an A if possible.

And

Thank you for your "Correct" answer, Mr. acperkins, and I will definately ponder your thoughts even though I am fairly comfortable in how I am doing it.

PS. I think 500 points was a very generous "gift" for a question such as this.  Any comments?

0
 
Anthony PerkinsCommented:
>>I will definately ponder your thoughts even though I am fairly comfortable in how I am doing it.<<
Fair enough.  Let me know if you need an explanation as to why using global XPath is problematic at best.

>>Any comments?<<
Please post your request in the right Topic Area:
http://www.experts-exchange.com/Community_Support/

See here for a full explanation from the EE Guidelines:

Can I get a grade changed?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi18
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now