MSDN Magazine > Issues and Downloads > 2002 > August >  Web Q&A: Passing XML to SQL Server, Document.wr...
From the August 2002 issue of MSDN Magazine
MSDN Magazine
Passing XML to SQL Server, Document.write on Resize, Transformations on the Fly, and More
Edited by Nancy Michell Web Q&A Archive
Download the code for this article: WebQA0208.exe (36 KB)
Q What's the best way to write data back to SQL Server™? Writing stored procedures seems reasonable using OPENXML, but it also seems that I have to manually specify the structure of my XML document every time I want to use OPENXML. What is the best way to pass XML back to SQL Server? Is there a way to specify a schema file when I do complex inserts or updates with OPENXML inside a stored proc?
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc 
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2) 
 WITH (      OrderID int '../@OrderID', 
                 CustomerID varchar(10) '../@CustomerID', 
                 OrderDate datetime '../@OrderDate', 
                 ProdID int '@ProductID', 
                 Qty int '@Quantity')
A OPENXML is a powerful tool for manipulating XML documents for complex inserts and analysis, but has significant overhead compared to using mapping schemas to associate elements and attributes directly to a column in a view or table. You can cache the schema in your virtual directory and use updategrams to modify your table or view.
      Using a stored procedure that includes a SELECT statement with the appropriate FOR XML clause will give you all of the flexibility that you need to pour any relational data into a semi-structured XML form.
      SQL Server 2000 Books Online does a great treatment on the SELECT...FOR XML options and the concepts behind a mapping schema, also known as an XML-Data reduced (XDR) schema.

Q My script does not run if I resize Netscape, but it's fine in Microsoft® Internet Explorer. In fact, it seems Netscape never reruns document.write on resize. But, if you reload the page, the script is executed. I tried setting a no-cache meta tag and delaying execution, but neither worked. Here is my code:
<body>

<script>
document.open( ); 
document.write('This is text created on the fly!
document.close( );
</script>

</body>
A This is a known behavior in Navigator. One workaround is to drop the document.write function into a separate .js source file. However, why not just reload the page on resize? Navigator does this anyway, so why not force a clean reload?
<body onresize="location.reload(true);">
Q Is there any way to apply XSL to an XML document when there is no tag in the XML document pointing to the XSL document? In other words, what if the following is omitted in the XML document?
<?xml-stylesheet type="text/xsl" href="xsl page" ?>
A When there's no XSL document specified, you can use the transformNode method from the DOM to do the transformation programmatically. If you are using Visual Basic®, here is what you do:
Dim xmlDoc as new MSXML2.DomDocument
Dim xslDoc as new MSXML2.DomDocument

xmlDoc.async = false
xmlDoc.load "somefile.xml"

xslDoc.async = false
xslDoc.load "somefile.xsl"

Debug.Print xmlDoc.transformNode(xslDoc)
The ASP code will look something like this:
<%
Dim xmlDoc, xslDoc
Dim xmlFile, xslFile

Set xmlDoc = Server.CreateObject("MSXML2.DOMDOCUMENT")
Set xslDoc = Server.CreateObject("MSXML2.DOMDOCUMENT")

xmlFile = "authors.xml"
xslFile = "authors.xsl"    

xmlDoc.async = false
xmlDoc.load Server.MapPath(xmlFile)

xslDoc.async = false
xslDoc.load Server.MapPath(xslFile)

Response.Write xmlDoc.transformNode(xslDoc)
%>
Q Many of our XML files have tag combinations such as this
<ui>Tables and Borders</ui> <defterm id="IDH_oftipToolbar">toolbar
</defterm>
where there is a space between the </ui> and <defterm>. When you transform the file with XSL, the space carries through to the HTML output, but when you preview the XML in Internet Explorer the space is dropped and the text runs together. Even when I used <xsl:preserve-space elements="*"/> in the XSL, it still had no effect on the preview. Does Internet Explorer turn whitespace off and ignore the XSL reference in the XML document <?xml-stylesheet type="text/xsl" href="../../../supportfiles/awsenduser.xsl"?>?

A Internet Explorer ultimately understands the document as HTML regardless of what transformations took place before. When you view XML in the browser, that XML has gone through a transformation using a default stylesheet that collapses whitespace. Whitespace between the tags, as well as leading and trailing whitespace within tags and attributes, is collapsed and other whitespace is normalized to a single space.
      If you're not using the default Internet Explorer view, you can control whitespace display through XSL. Take this XML snippet:
<b>Mike</b>      <i>Birch</i> is same as <b>Mike</b><i>Birch</i>
<a>Mike   </a> is same as <a>Mike</a>
<a>Mik     e</a> is viewed as <a>Mik e</a>
To specifically introduce a nonbreaking whitespace you would use &nbsp; in HTML. In XSL you have to use
<xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text>
to output &nbsp;. The <xsl:preserve-space> directive applies to the content of elements specificed by the elements attribute, and doesn't apply to the whitespace between the tags. See White Space and the DOM for more information on this topic.

Q I am having trouble passing an XPath expression (an attribute name) to an XSL document in ASP.NET using the AddParam method of an XsltArgumentList. The relevant ASPX code snippet is listed here. (Xml1, shown in the following code, is an XML Web server control on the Web form.)
/* --WebForm1.aspx.cs-- */
XsltArgumentList args = new XsltArgumentList();
XslTransform xslDoc = new XslTransform();
xslDoc.Load(xslPath);
args.AddParam("strQuery","","UserInput");
args.AddParam("strQType","","@Kw");
Xml1.DocumentSource = "find.xml";
Xml1.TransformArgumentList = args;
Xml1.TransformSource = "find.xsl";
And the XSL procedure I'm using is listed here:
/* --Find.XSL -- */
<xsl:param name="strQuery"/>
<xsl:param name="strQType"/>
<xsl:if test="contains($strQType,$strQuery)">
/* --Process this node-- */
</xsl:if>
      I want the XSLT contains function to compare a user input string to the value of a selected attribute of the current node. The strQuery parameter is passed correctly (it's just a string), but I can't get the XSL code to utilize the strQType parameter as an attribute name. If I hardcode the attribute name into the strQType parameter, everything works fine:
<!--Find.XSL-->
<xsl:param name="strQuery"/>
<xsl:param name="strQType" select="@Kw"/>
<xsl:if test="contains($strQType,$strQuery)">
<!--Process this node-->
</xsl:if>
Thus the XSL code must be correct and something must be getting lost in passing the parameter from the Web page to the XSL.

A Your code is passing the parameter as a string, so XSLT will not treat it as an XPath expression. To have XSLT treat the parameter as a nodeset, you have to pass in an XPathNodeIterator. But that means you have to execute the XPath expression before the XSLT.
      Here's the simplest way to refer to a node name by a string:
<xsl:param name="strQuery"/>
<xsl:param name="strQType"/>
<xsl:if test="contains(@*[name()=$strQType],$strQuery)">
Don't forget to consider the "attribute not present" case if you're using the contains directive as a test. If the attribute doesn't exist then contains will create an empty nodeset, which converts to an empty string, which evaluates to TRUE in the contains function:
<xsl:if test="@*[name()=$strQType] and 
  contains(@*[name()=$strQType],$strQuery)">
Q How can I set a cookie with ServerHttpRequest? SetRequestHeader seems to ignore the cookie header I am setting. I tried setting it this way
setRequestHeader(L"cookie", L"your-cookie-goes-here");
but the cookie header was never sent out. WinHTTP for MSXML 3.0 seems to delete all cookie headers set by the caller, inserting its own cookies instead.

A Try setting the cookies twice. The Knowledge Base article Q234486 (XMLHttpRequest setRequestHeader Method and Cookies) discusses this anomaly:
'this value is ignored, but the step is necessary
xmlRequest.setRequestHeader "Cookie", "any non-empty string here"
'set all cookies here
xmlRequest.setRequestHeader "Cookie", "cookie1=value1; cookie2=value2"
As the article states, though, setting cookies in this way is not recommended. Cookies should be set by the server using the Set-Cookie header whenever possible.

Q I need to query my document for the namespaces it uses. For instance, take the following fragment:
<Schema name="BlankSpecification" xmlns="urn:schemas-microsoft-com:xml-
  data"
  xmlns:b="urn:schemas-microsoft-com:BizTalkServer"
  xmlns:d="urn:schemas-microsoft-com:datatypes">
</Schema>
      I need to create an XPath query to return all of the xmlns items. What should the query look like?

A Since this question comes up often, an XPath query is provided in Figure 1. In the code download at the link at the top of this article you'll also find a script that will return an XML document containing all the unique namespaces (by URI) in a document. It works by gathering all namespace nodes in a document into one variable, then outputting only those with a unique URI.

Q Is there a way to perform a toLowerCase on an XML node? I am trying to pull a case-sensitive node from an XML file , but I want to pass in all lowercase IDs. Is there a way that I can perform my query so that my application does not need to be case-specific when querying? If I use the following
<build id="SomeNameHere">
    <logfile file="x.log" size="1288758"/>
    <logfile file="y.log" size="684996"/>
</build>
the query looks like this:
oXMLFile.selectSingleNode("//build[@id = '" + Passed_In_Parameter + "']")
A If you're using MSXML 4.0, use the ms:string-compare function to achieve case-insensitivity (see ms:string-compare Function). Use the option i to do the case-insensitive comparison. If you're using the .NET System.XML classes, use the standard XSLT translate function (but it's ugly).

Q Is there any way to keep people from looking at include files or the code in JScript® files on Web sites?

A If you're using ASP, you can download a tool called the Script Encoder from http://msdn.microsoft.com/scripting. If you're including ASP code (such as VBScript), you might consider mapping the .inc extension to the ASP.dll executable. You will still be able to include the files in your ASP pages, but instead of opening like text files when viewed directly, they will be interpreted by ASP.dll just like ASP pages, so the code will be "invisible" to the user.
      To add this mapping in IIS, go to the Internet Information Services MMC snap-in console, right-click Web Site or Virtual Directory and get the Properties. Choose Home Directory (or just Directory, in the case of a virtual directory), and click on the Configuration button. On the App Mappings tab, select Add, then enter the path to ASP.dll and the .inc extension.
      Apply this, and IIS will use ASP.dll to process requests to view .inc files, rather than rendering into text. It's worth noting that only the contents of the include file in the <% %> tags (regardless of where .inc is mapped) will be hidden/interpreted as ASP code. Anything outside the tags will pass through untouched. This code
<%
 asp code here
%>
<a href="admin.asp">secret link to admin page</a>
would display "secret link to admin page" as a link. The same goes for any commented HTML code. A similar solution is to use the .asp extension rather than .inc, since it is already protected by IIS.

Got a question? Send questions and comments to webqa@microsoft.com.
Thanks to the following Microsoft developers for their technical expertise: Umut Alev, Jay Allen, Mike Birch, Allan Brown, John Caricato, Steve Cawood, Martin Chamberlain, Zhong Chen, Patrick Coelho (Excell Data Corporation), Daemond Coulic, Vlad Eydlman, Jeff Gustafson, Dion Houston, Pranav Kandula, Sowmya Lowe (Accenture), Michael Miller, Andrew Minkin, Jonathan Moons, Andy Morrison, Bulent Ozkir, Paul Peavyhouse, Ryan Rogers (Siemens Business Services), Kuen Siew, Charles Torre, Phuong Tran, and Rusty Van Zandt.

Page view tracker