I have a SP set up to take an input param and then fire the following
sp_xml_preparedocument
OPENXML (With a select statement)
sp_xml_removedocument
This works fine with the XML syntax that microsoft provides in its
documentation ...
<ROOT>
<Customer>
<CustomerID>VINET</CustomerID>
<ContactName>Paul Henriot</ContactName>
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer>
<CustomerID>LILAS</CustomerID>
<ContactName>Carlos Gonzlez</ContactName>
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>
The problem I am having is parsing XML data given to me in this format
<?xml version="1.0" standalone="yes"?>
<Active_x0020_Directory_x0020_Users>
<Groups_Duz_x0060_mp>
<whenCreated>2/13/2004 7:13:21 PM</whenCreated>
<whenChanged>2/13/2004 7:13:21 PM</whenChanged>
<sAMAccountname>!WGSyEnBuCoBuDoCG</sAMAccountname>
<cn>!WGSyEnBuCoBuDoCG</cn>
<groupType>-2147483646</groupType>
</Groups_Duz_x0060_mp>
<Groups_Duz_x0060_mp>
<whenCreated>2/13/2004 7:12:04 PM</whenCreated>
<whenChanged>2/13/2004 7:12:04 PM</whenChanged>
<sAMAccountname>#11SeanTest</sAMAccountname>
<cn>#11SeanTest</cn>
<groupType>8</groupType>
</Groups_Duz_x0060_mp>
</Active_x0020_Directory_x0020_Users>
As you can see the elements are defined differently, I have an XSL
file as follows
<?xml version="1.0" standalone="yes"?>
<xs:schema id="Active_x0020_Directory_x0020_Users" xmlns=""
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="Active_x0020_Directory_x0020_Users"
msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Groups_Duz_x0060_mp">
<xs:complexType>
<xs:sequence>
<xs:element name="whenCreated" type="xs:string"
minOccurs="0" />
<xs:element name="whenChanged" type="xs:string"
minOccurs="0" />
<xs:element name="sAMAccountname" type="xs:string"
minOccurs="0" />
<xs:element name="cn" type="xs:string" minOccurs="0" />
<xs:element name="groupType" type="xs:string"
minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
So now I am wondering how I tell SQL Server to use the new file format
for XML?
Thanks in advance for your help.