Creating View With Xml file and table in sql server 2005

5 pts.
Tags:
Active Directory
SQL Server 2005
XML
Hi, I have an Active Directory Xml File and I have archived Table with Active Directory With Limited Information like(GUI,FName, Userid) I like to create a view By left join active directory file and archived table. Thanks for reply in advance Please reply to this question
ASKED: July 14, 2008  6:37 PM
UPDATED: July 14, 2008  11:27 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

You aren’t going to be able to make a view to this XML file easily as to use an XML file directly you have to run the sp_xml_prepairedocument and the sp_xml_removedocument procedures. I would recommend making a stored procedure instead of a view which then returns the data you are looking for.

If a view is required, you could setup a linked server to the XML file, and do a join to that XML file, but I’m not sure what sort of file locking a linked server will place on the XML file preventing you from updating it.

The stored procedure would look something like this.

<pre>CREATE PROCEDURE ShowXML AS

CREATE TABLE #RawXML (XMLData XML)

BULK INSERT #RawXML FROM ‘D:\YourXMLFile.XML’ WITH RAW

DECLARE @RawXML XML

SELECT @RawXML = XMLData
FROM #RawXML

DECLARE @hDoc INT

EXEC sp_xml_preparedocument @hDoc OUTPUT @RawXML

SELECT *
FROM YourArchiveTable
JOIN OPENXML (@hDoc, ‘//’)
WITH (Guid uniqueidentifier ‘@Guid’,
FName NVARCHAR(50) ‘@FName’,
UserId NVARCHAR(50) ‘@UserId’) a ON YourArchiveTable.UserId = a.UserId

EXEC sp_xml_removedocument @hDoc
GO</pre>

Keep in mind that this isn’t tested and will probably need a bit of tweaking based on your XML file and table setup.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following