SQL for xml query sample, xmlsample

SQL for xml query sample, xmlsample

Sample 1:

declare @x xml   select @x='<ArrayOfScheduledTime>  <ScheduledTime>    <RecurrenceType>EveryMonday</RecurrenceType>    <Stamp>      <dateTime>2000-01-01T19:30:00</dateTime>    </Stamp>  </ScheduledTime>  <ScheduledTime>    <RecurrenceType>EveryThursday</RecurrenceType>    <Stamp>      <dateTime>2000-01-01T19:30:00</dateTime>    </Stamp>  </ScheduledTime>  <ScheduledTime>    <RecurrenceType>EverySunday</RecurrenceType>    <Stamp>      <dateTime>2000-01-01T19:30:00</dateTime>    </Stamp>  </ScheduledTime></ArrayOfScheduledTime>'SELECT N.v.value('.' , 'VARCHAR(100)')B FROM @x.nodes('/ArrayOfScheduledTime/ScheduledTime/RecurrenceType')N(v)/*EveryMondayEveryThursdayEverySunday*/

select @x.query('/ArrayOfScheduledTime/ScheduledTime/RecurrenceType')/*<RecurrenceType>EveryMonday</RecurrenceType><RecurrenceType>EveryThursday</RecurrenceType><RecurrenceType>EverySunday</RecurrenceType>*/

Select o. value ('recurrencetype [1] ', 'varchar (20)') 'recurrencetype 'from (select @ x 'X') t cross apply x. nodes ('/ArrayOfScheduledTime/ScheduledTime') x (o)/* RecurrenceType ------------------ EveryMondayEveryThursdayEverySunday (3 rows affected )*/

DECLARE @ handel int; EXEC sp_xml_preparedocument @ handel output, @ xSELECT * from OPENXML (@ handel, '/ArrayOfScheduledTime/ScheduledTime', 2) WITH (RecurrenceType varchar (50 )) /***************************** EveryMondayEveryThursdayEverySunday */

Sample2:

DECLARE @xDailyConfig XMLset @xDailyConfig='<ArrayOfScheduledTime>  <ScheduledTime>    <RecurrenceType>Everyday</RecurrenceType>    <Stamp>      <dateTime>2000-01-01T09:00:00</dateTime>      <dateTime>2000-01-01T13:00:00</dateTime>      <dateTime>2000-01-01T19:00:00</dateTime>    </Stamp>  </ScheduledTime></ArrayOfScheduledTime>'SELECT N.v.value('.' , 'VARCHAR(100)')B FROM @xDailyConfig.nodes('/ArrayOfScheduledTime/ScheduledTime/Stamp/dateTime')N(v)/*2000-01-01T09:00:002000-01-01T13:00:002000-01-01T19:00:00*/

Temporary xml generated from the database using SQL for xml statements

Js uses this to parse xml:
Var xmlDoc = new ActiveXObject ('msxml2. domdocument ');
XmlDoc. async = false;
XmlDoc. loadXML (xml );
Var xmlRoot = xmlDoc.doc umentElement;
// Retrieve the Element Object
Var Nodes = xmlRoot. getElementsByTagName ("Nodes"). item (0 );
// Traverse
For (var I = 0; I <Nodes. childNodes. length; I ++ ){
Node = Nodes. childNodes. item (I );
// Value
Node. getElementsByTagName ('baseproperties'). item (0). getAttribute ('id ');
}
Xml format: <Nodes> <Node> <BaseProperties id = "N1234 "...

SQL for xml !!!!!!!!!!!!!!!