Using OpenXML

Question

Post reply

Using OpenXML

Zakat007

Grasshopper

Points: 16
More actions
August 19, 2010 at 9:00 am

#222758

DECLARE @IDDocument INT
DECLARE @Document NVARCHAR(MAX)
SET @Document ='<entry xmlns="http://www.w3.org/2005/Atom">
<link href="/ws/customers/wonmktg/contacts/15983" rel="edit"></link>
<id>http://api.constantcontact.com/ws/customers/wonmktg/contacts/15983</id>
<title type="text">Contact: mark.arant@acf.hhs.gov</title>
<updated>2010-06-29T22:24:51.770Z</updated>
<author>
<name>Constant Contact</name>
</author>
<content type="application/vnd.ctct+xml">
<Contact id="http://api.constantcontact.com/ws/customers/wonmktg/contacts/15983" xmlns="http://ws.constantcontact.com/ns/1.0/">
<Status>Active</Status>
<EmailAddress>mark.arant@acf.hhs.gov</EmailAddress>
<EmailType>HTML</EmailType>
<Name>MARK ARANT</Name>
<FirstName>MARK</FirstName>
<MiddleName></MiddleName>
<LastName>ARANT</LastName>
<JobTitle></JobTitle>
<CompanyName></CompanyName>
<HomePhone></HomePhone>
<WorkPhone></WorkPhone>
<Addr1></Addr1>
<Addr2></Addr2>
<Addr3></Addr3>
<City></City>
<StateCode>MO</StateCode>
<StateName>Missouri</StateName>
<CountryCode>us</CountryCode>
<CountryName>United States</CountryName>
<PostalCode></PostalCode>
<SubPostalCode></SubPostalCode>
<Note></Note>
<CustomField1>271612808701441</CustomField1>
<CustomField2>11/1/09</CustomField2>
<CustomField3>No</CustomField3>
<CustomField4>Yes</CustomField4>
<CustomField5></CustomField5>
<CustomField6></CustomField6>
<CustomField7></CustomField7>
<CustomField8></CustomField8>
<CustomField9></CustomField9>
<CustomField10></CustomField10>
<CustomField11></CustomField11>
<CustomField12></CustomField12>
<CustomField13></CustomField13>
<CustomField14></CustomField14>
<CustomField15></CustomField15>
<ContactLists>
<ContactList id="http://api.constantcontact.com/ws/customers/wonmktg/lists/39">
<link href="/ws/customers/wonmktg/lists/39" rel="self" xmlns="http://www.w3.org/2005/Atom"></link>
<OptInSource>ACTION_BY_CUSTOMER</OptInSource>
<OptInTime>2010-05-28T23:56:10.279Z</OptInTime>
</ContactList>
<ContactList id="http://api.constantcontact.com/ws/customers/wonmktg/lists/57">
<link href="/ws/customers/wonmktg/lists/57" rel="self" xmlns="http://www.w3.org/2005/Atom"></link>
<OptInSource>ACTION_BY_CUSTOMER</OptInSource>
<OptInTime>2010-06-29T22:24:51.769Z</OptInTime>
</ContactList>
</ContactLists>
<Confirmed>false</Confirmed>
<InsertTime>2010-05-28T23:56:10.271Z</InsertTime>
<LastUpdateTime>2010-06-29T22:24:51.770Z</LastUpdateTime>
</Contact>
</content>
<source>
<id>http://api.constantcontact.com/ws/customers/wonmktg/contacts</id>
<title type="text">Contacts for Customer: wonmktg</title>
<link href="contacts"></link>
<link href="contacts" rel="self"></link>
<author>
<name>wonmktg</name>
</author>
<updated>2010-08-18T23:39:14.346Z</updated>
</source>
</entry>'
SELECT @Document
EXEC sp_xml_preparedocument @IDDocument OUTPUT, @Document
SELECT *
FROM OPENXML(@IDDocument, '/entry/content/Contact', 2)
WITH(Status VARCHAR(30))
SELECT *
FROM OPENXML(@IDDocument, '', 2)
EXEC sp_xml_removedocument @IDDocument

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

What are you trying to do? Your last OpenXML statement returns data. What are the expected results?

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 2

The first select returns nothing, perhaps he is asking about that select.

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 3

"xmlns" attributes within root element and Contact element are causing the problem.

But I do not know why!

If those attributes are removed then the second SELECT statement will return Status element value.

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 4

OK, I studied some

If you remove the below attribute value from Contact element

xmlns="http://ws.constantcontact.com/ns/1.0/"

And change sql script as follows then it will work

EXEC sp_xml_preparedocument @IDDocument OUTPUT, @Document, '<ROOT xmlns:n="http://www.w3.org/2005/Atom"/>'

SELECT * FROM OPENXML(@IDDocument, '//n:content/n:Contact', 2) WITH ([n:Status] VARCHAR(30))

Please refer to http://sqlxml.org/faqs.aspx?faq=101 for sample case

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

LutzM SSC Guru Points: 107049 More actions · Answer 5

What is your expected output based on the sample data provided?

Since you have a named xml doc you need to use the namespace declaration in your query.

I don't know if that's possible at all using OPENXML but it's definitely possible with XQuery.

If you'd like to see a coded sample based on your data please provide the expected result.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 6

I guess he is trying to get the "Active" text from "<Status>Active</Status>" fragment

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

LutzM SSC Guru Points: 107049 More actions · Answer 7

Having a closer look at your document it seems like there is an inconsistent default namespace declaration:

< entry xmlns="http://www.w3.org/2005/Atom" >

< Contact xmlns="http://ws.constantcontact.com/ns/1.0/" >

Either you set those two elements to the same namespace or you define separate namespaces.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Zakat007 Grasshopper Points: 16 More actions · Answer 8

I figured it out I will post the code.

DECLARE @XmlDocument XML

DECLARE @XmlDocumentID INT

EXECUTE sp_xml_preparedocument @XmlDocumentID OUTPUT, @XmlDocument, '<entry xmlns:x="http://www.w3.org/2005/Atom" xmlns:y="http://ws.constantcontact.com/ns/1.0/"/>'

SELECT [ID], [Link], [LinkRel], [Status]

FROM OPENXML(@XmlDocumentID, 'x:entry', 2)

WITH

(

[ID] NVARCHAR(256) 'x:id',

[Link] NVARCHAR(256) 'x:link/@href',

[LinkRel] NVARCHAR(256) 'x:link/@rel',

[Status] NVARCHAR(256) 'x:content/y:Contact/y:Status'

)

EXECUTE sp_xml_removedocument @XmlDocumentID

Thank you I got it working this way.

LutzM SSC Guru Points: 107049 More actions · Answer 9

Is there any specific reason for still using OPENXML over XQuery?

How about comparing the performance against the following XQuery statement?

SELECT

v.value('id[1]','NVARCHAR(256)') AS ID,

x.value('@href[1]','NVARCHAR(256)') AS [Link],

x.value('@rel[1]','NVARCHAR(256)') AS [LinkRel],

z.value('Status[1]','NVARCHAR(256)') AS [Status]

FROM @document.nodes('entry[1]') T(c)

CROSS APPLY

c.nodes('source[1]') AS U(v)

CROSS APPLY

c.nodes('link[1]') AS W(x)

CROSS APPLY

c.nodes('content[1]/Contact') AS Y(z)

/* result set:

IDLinkLinkRelStatus

http://api.constantcontact.com/ws/customers/wonmktg/contacts/ws/customers/wonmktg/contacts/15983editActive

*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]