Help needed using Openxml in SQL2008

Question

Hi,

I have a .net datatable object that I serialized into an xml to pass it to a stored proc. The xml looks like this:

<?xml version="1.0"?>
<DataTable>
  <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    <NewDataSet>
      <CompanyList diffgr:id="CompanyList1" msdata:rowOrder="0">
        <CompanyName>C1</CompanyName>
        <CompanyType>T1</CompanyType>
      </CompanyList>
      <CompanyList diffgr:id="CompanyList2" msdata:rowOrder="1">
        <CompanyName>C2</CompanyName>
        <CompanyType>T1</CompanyType>
      </CompanyList>
    </NewDataSet>
  </diffgr:diffgram>
</DataTable>

How do I use OpenXml to populate CompanyName and CompanyType values into a table?

---

amateur professionals

Answer 1

Try:

DECLARE @x xml;

SET @x = '<?xml version="1.0"?>
<DataTable>
 <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
  <NewDataSet>
   <CompanyList diffgr:id="CompanyList1" msdata:rowOrder="0">
    <CompanyName>C1</CompanyName>
    <CompanyType>T1</CompanyType>
   </CompanyList>
   <CompanyList diffgr:id="CompanyList2" msdata:rowOrder="1">
    <CompanyName>C2</CompanyName>
    <CompanyType>T1</CompanyType>
   </CompanyList>
  </NewDataSet>
 </diffgr:diffgram>
</DataTable>';

WITH XMLNAMESPACES('urn:schemas-microsoft-com:xml-diffgram-v1' AS diffgr)
SELECT
  C.x.value('(CompanyName/text())[1]', 'varchar(50)') AS CompanyName,
  C.x.value('(CompanyType/text())[1]', 'varchar(50)') AS CompanyType
FROM
	@x.nodes('(DataTable/diffgr:diffgram/NewDataSet)[1]') AS R(x)
	CROSS APPLY
  R.x.nodes('CompanyList') AS C(x);
GO

---

AMB

Some guidelines for posting questions...

Answer 2

Thank you Hunchback!! This works!!

I'm sorry but I'm new to xml programming on SQL, so would you explain what is being done here? Also is this the only way to read the xml or is there a way to use OpenXml to read xmls with schema information?

Thanks a bunch again! 

---

amateur professionals

Answer 3

We have the xml data type from SS 2005, so I will suggest to start using the type methods.

Basically what I did was to declare an xml space name in order to be able to use the prefix, and then get the "CompanyList" nodes. Then use the value() method to extract the value of each element.

If you are new to xml inside SQL Sever, then I recommend these web sites.

http://beyondrelational.com/blogs/jacob/archive/2008/06/26/xquery-labs-a-collection-of-xquery-sample-scripts.aspx

http://bradsruminations.blogspot.com

 

 

---

AMB

Some guidelines for posting questions...

Answer 4

Thanks Hunchback, that helps!!

---

amateur professionals