Solved

OpenXML with namespace and schema

Posted on 2006-07-10
13
789 Views
Last Modified: 2013-11-19
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
Comment
Question by:awiinc
  • 7
  • 5
13 Comments
 
LVL 35

Accepted Solution

by:
YZlat earned 300 total points
Comment Utility
try

SELECT APPROVAL_INDICATOR,CODE,MESSAGE, .....
FROM OPENXML (@hdoc, '/NewDataSet/Table1',1)
0
 
LVL 2

Author Comment

by:awiinc
Comment Utility
YZlat,
Thanks for the quick response, but this returns nothing.  The namespaces, I believe are making this a bit more extraordinary.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
Comment Utility
You need to include the namespace in the sp_xml_preparedocument command, as well as modify the OPENXML appropriately.
0
 
LVL 2

Author Comment

by:awiinc
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Post your OPENXML as it stands now.
0
 
LVL 2

Author Comment

by:awiinc
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Author Comment

by:awiinc
Comment Utility
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
 
LVL 2

Author Comment

by:awiinc
Comment Utility
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
 
LVL 2

Author Comment

by:awiinc
Comment Utility
Although none of the answers were correct, I appreciate your help and quick responses.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Any comments?
0
 
LVL 2

Author Comment

by:awiinc
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now