473,324 Members | 2,501 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Using OPENXML

JM
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.
Jul 20 '05 #1
2 7736

"JM" <j_*******@hotmail.com> wrote in message
news:c8**************************@posting.google.c om...
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 ...


<snip>

You may want to post this in microsoft.public.sqlserver.xml, to get a better
answer. I'm not at all sure about this, but I believe there's support for
using XSLs in the SQLXML toolkit.

Simon
Jul 20 '05 #2
JM (j_*******@hotmail.com) writes:
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 ...
...
The problem I am having is parsing XML data given to me in this format
...

As you can see the elements are defined differently, I have an XSL
file as follows


Not exactly sure what problem you have. This gave me a result set:

declare @x nvarchar(4000), @doc int
select @x = N'<?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>'

exec sp_xml_preparedocument @doc output, @x

select * from openxml(@doc,
'/Active_x0020_Directory_x0020_Users/Groups_Duz_x0060_mp')
with (whenCreated datetime 'whenCreated',
sAMAccountname nvarchar(200) 'sAMAccountname',
cn nvarchar(200) 'cn',
groupType int 'groupType')

exec sp_xml_removedocument @doc

Now, how to use the XSL file is another story. That is probably not
possible with the XML support that comes with SQL Server. However,
poke around at www.microsoft.com and you should find version 4 of
SQLXML, with a lot better support for XSLT and all that.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: jrd | last post by:
The below code is only pulling the outcome_id value, but is pulling back null for the other fields in the xml string, any ideas on what is going on? -- Prepare xml data to be transfered into an...
3
by: Szaki | last post by:
I must import some exemplary file to database (MS Srrver 2000) ofcourse using procedure Transact SQL. This file must: 1.Read the xml file 2. Create table 3. Import this date from xml file to my...
2
by: Bostonasian | last post by:
I've got thousands of XML docs and have to import those to the DB. And I am having problem with getting some values because of tricky XML format. I didn't create this XML format and I don't like...
6
by: Andrzej | last post by:
Used to read newsgroup for answers, now have to ask for them as well. I have an application (C#, .NET 1.1) that connects to local db on MSDE 2000 SP3a (using ADO from MDAC 2.71) on one side and...
0
by: Micke | last post by:
On a code behing page to an aspx page I want to open a xml file in Excel and save it as an Excel file. I have following code that is working: Dim oExcel As New Excel.Application Dim oBook As...
2
by: Shilpa | last post by:
Hi All, I want to pass XML and the data in the XML should be stored in the tables of the database. However, I do not want to use the OpenXML statement. Please let me know. Regards, Shilpa
0
StarPilot
by: StarPilot | last post by:
In SQL Server I can pass an XML document into a sproc, run it through sp_xml_preparedocument and then use the OPENXML() function to use it in a query. For example: DECLARE @idoc int DECLARE @doc...
1
by: yingwen | last post by:
I have a xml similar like this: <Member ID="123"> <DateBorrowed>11-01-2006</DateBorrowed> <Book ID="222" Title="ABC"> <Category> Fiction</Category> </Book>...
5
by: emsik1001 | last post by:
I'm trying to upload XML into SQL Server 2000. I've never used openxml and I'm struggling with it. Below is the procedure with data. DECLARE @idoc int DECLARE @doc varchar (1000) SET @doc...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.