OpenXML - attribute centric XML

Surprsingly a sample for this one was hard to find in internet, i googled for a long time to catch a working code. The column i joined was a char and has spaces in before and i needed to keep the spaces for joining to that column, normal element centric approach will trim the spaces while preparing the document to table structure.

CREATE TABLE OpenXMLTest
(
VDR_NUM CHAR(9) PRIMARY KEY,
VDR_NAME CHAR(100)
)
GO


INSERT INTO OpenXMLTest VALUES(' 1','Contoso Inc.')
GO

CREATE PROC TestOpenXML
@in_TXML text
as
DECLARE @hDoc INT
EXEC sp_xml_preparedocument @hDoc OUTPUT, @in_TXML
SELECT A.VDR_NUM, A.VDR_NAME
FROM OpenXMLTest A,
OPENXML (@hDoc, 'ROOT/OpenXMLTest',2)
WITH(VDR_NUM CHAR (9)) OutXML
WHERE A.VDR_NUM = OutXML.VDR_NUM

//cs code

System.Text.StringBuilder sb = new System.Text.StringBuilder(1000);
XmlTextWriter xtWriter=new XmlTextWriter(new System.IO.StringWriter(sb));
xtWriter.WriteStartElement("ROOT");
xtWriter.WriteStartElement("OpenXMLTest");
xtWriter.WriteStartElement("VDR_NUM");
xtWriter.WriteCData(" 1");
xtWriter.WriteEndElement(); //for VDR_NUM
xtWriter.WriteEndElement(); //for OpenXMLTest
xtWriter.WriteEndElement(); //for ROOT

// mundane ADO.net code goes here, use sb.ToString() for Xml string