?
Solved

OpenXML with namespace and schema

Posted on 2006-07-10
13
Medium Priority
?
818 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
The viewer will learn how to count occurrences of each item in an array.
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 …

752 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