Solved

OpenXML with namespace and schema

Posted on 2006-07-10
13
811 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 300 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 200 total points
ID: 17075104
You need to include the namespace in the sp_xml_preparedocument command, as well as modify the OPENXML appropriately.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Using Sum with Case When within a query 9 44
SQL Convert rows to columns 5 31
Remove () 9 36
File attachment in the SQL Database from application 10 43
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

739 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