Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

OpenXML with namespace and schema

Posted on 2006-07-10
13
Medium Priority
?
824 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 35

Accepted Solution

by:
YZlat earned 600 total points
ID: 17075017
try

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

Author Comment

by:awiinc
ID: 17075067
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 400 total points
ID: 17075104
You need to include the namespace in the sp_xml_preparedocument command, as well as modify the OPENXML appropriately.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 2

Author Comment

by:awiinc
ID: 17075346
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
ID: 17075459
Post your OPENXML as it stands now.
0
 
LVL 2

Author Comment

by:awiinc
ID: 17075666
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
 
LVL 2

Author Comment

by:awiinc
ID: 17075700
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
ID: 17075823
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
ID: 17075843
Although none of the answers were correct, I appreciate your help and quick responses.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17076189
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
ID: 17095490
Any comments?
0
 
LVL 2

Author Comment

by:awiinc
ID: 17099187
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
ID: 17099635
>>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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

650 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