SQL statement fetch xml

DECLARE @ docHandle int;
DECLARE @ xmlDocument nvarchar (max); -- or xml type

SET @ xmlDocument = n' <ROOT>
<Customers CustomerID = "XYZAA" ContactName = "Joe" CompanyName = "Company1">
<Orders CustomerID = "XYZAA" OrderDate = "2000-08-25T00: 00: 00"/>
<Orders CustomerID = "XYZAA" OrderDate = "2000-10-03T00: 00: 00"/>
</Customers>
<Customers CustomerID = "XYZBB" ContactName = "Steve"
CompanyName = "Company2"> No Orders yet!
</Customers>
</ROOT> ';

EXEC sp_xml_preparedocument @ docHandle OUTPUT, @ xmlDocument;
-- Use OPENXML to provide rowset consisting of customer data.

SELECT * from openxml (@ docHandle, n'/Request/DataSet/dsForm/form') WITH (UserID nchar (10), ApplyDate datetime );
EXEC sp_xml_removedocument @ docHandle;

 

 

DECLARE @ idoc int
DECLARE @ doc varchar (max), @ doc2 varchar (max)
Select @ doc2 = '<Request> <Adapter> <Application> CanLearn </Application> <Version> 5.x</Version> <AuthenticationParser> CET </AuthenticationParser> <AuthenticationCode> ABCDE-FGHIJ-KLMNO-PQRST-UVWXY_CET </ authenticationCode> </Adapter> <Connection> <ConnectionGUID> quit </ConnectionGUID> <Sender> <SenderName> tpeelap </SenderName> <SenderIP> 10.110.15.43 </SenderIP> </Sender> </Connection> <Com MandSet> <OperationGUID> E92483FC-30CB-4880-956A-CABA12A86B09 </OperationGUID> <SiteName> EF2KWeb </SiteName> <ActionMode> createForm </ActionMode> <! --- KeySet provides the information recognition value --> <KeySet> <FormID> STDCLC </FormID> <UserID> 0402435 </UserID> <ParserRoleID> 0402435 </ParserRoleID> <Subject> Chang Hongmei's external application: 「 E-Form terraform 3 (no attachment available ~ (15 hours) </Subject> <Important/> </KeySet> <ConditionSet> <Cost> 1000 </Cost> <Nation> 1 </Nation> <! -- Representative of the country's representative Italian [] Taiwan region [] greater region [] others --> <DeptID> GPAA310 </DeptID> <Factory> G </Factory> <EmpDeptCode> </EmpDeptCode> <JobLevelID> 7.1 </JobLevelID> <OrgLevelCode> D070 </OrgLevelCode> </ConditionSet> <Attachments/> </CommandSet> <! -- Optional information description format --> <DataSchema> </DataSchema> <! -- Optional data set --> <DataSet> <dsForm xmlns = "http://www.tempuri.org/dsForm.xsd"> <Form> <CanLearnFormID> interval </CanLearnFormID> <ApplyType> 2 </ApplyType> <ApplyDate> 2005/09/30 </ApplyDate> <UserID> 0402435 </UserID> <CourseName> E-Form example Example 3 (no attachment exists) </CourseName> <Description> E-Form example Example 3 (no attachment is available) </Description> <Organization> cet </Organization> <StartDate> 2005/10/01 </StartDate> <EndDate> 2005/10/10 </EndDate> <Time>-</Time> <Hours> 15 </Hours> <UserCount> 1 </UserCount> <CostPerUser> 1000 </CostPerUser> <Money> CNY </Money> <Cost> 1000 </Cost> <CostLocal> 1000 </CostLocal> <ReportType> large transaction volume, renewal table </ReportType> <Nation> large tables </Nation> <ApplyReason> E-Form except tables 3 (no attachment available) </ApplyReason> <Org_URL> cetwww </Org_URL> <ApplySet> <Employee> <UserID> 0402435 </UserID> </Employee> </ApplySet> </Form> </ dsForm> </DataSet> </Request>'
Select @ doc = n' <? Xml version = "1.0" encoding = "big5"?> '+ Replace (convert (nvarchar (max), @ doc2), 'xmlns = "http://www.tempuri.org/dsForm.xsd"', '') -- from efclca where efclca002 = '2016'
EXEC sp_xml_preparedocument @ idoc OUTPUT, @ doc
Select * from
Openxml (@ idoc, '/Request/DataSet/dsForm/Form', 2)
With (CanLearnFormID varchar (1000 ));
Exec sp_xml_removedocument @ idoc