CODEWITHSUNDEEP
xmlsql-server-2008
Raju
Raju

Reputation: 1

Reading XML in SQL server 2008

I want to read the ContactId and Notes from the below XML. I tried using Openxml and declaring the namespace, but i could not get my expected result. Request you to provide the solution.

<ArrayOfContact xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Contact>
<ContactId xmlns="http://www.thomsonreuters.com/2011/02/25/CMS/CmsService/ContactData">100</ContactId>
<Notes xmlns="http://www.thomsonreuters.com/2011/02/25/CMS/CmsService/ContactData">Test1</Notes>
</Contact>
<Contact>
<ContactId xmlns="http://www.thomsonreuters.com/2011/02/25/CMS/CmsService/ContactData">101</ContactId>
<Notes xmlns="http://www.thomsonreuters.com/2011/02/25/CMS/CmsService/ContactData">Test2</Notes>
</Contact>
</ArrayOfContact>

Upvotes: 0

Views: 1280

Answers (1)

Lucero
Lucero

Reputation: 60190

The OPENXML funcation is legacy XML support for SQL Server 2000, I'd use the newer XML methods introduced on the xml datatype.

DECLARE @x xml;
SET @x = '<ArrayOfContact xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Contact>
<ContactId xmlns="http://www.thomsonreuters.com/2011/02/25/CMS/CmsService/ContactData">100</ContactId>
<Notes xmlns="http://www.thomsonreuters.com/2011/02/25/CMS/CmsService/ContactData">Test1</Notes>
</Contact>
<Contact>
<ContactId xmlns="http://www.thomsonreuters.com/2011/02/25/CMS/CmsService/ContactData">101</ContactId>
<Notes xmlns="http://www.thomsonreuters.com/2011/02/25/CMS/CmsService/ContactData">Test2</Notes>
</Contact>
</ArrayOfContact>';

WITH XMLNAMESPACES ('http://www.thomsonreuters.com/2011/02/25/CMS/CmsService/ContactData' AS cms)
SELECT
    contact.value('cms:ContactId[1]', 'int') ContactId, 
    contact.value('cms:Notes[1]', 'nvarchar(MAX)') Notes 
    FROM @x.nodes('/ArrayOfContact/Contact') AS contacts(contact);

Upvotes: 2

Related Questions