Category Archives: SQLXML

SQLXML : How to Use SQL Server XML Function exist()

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

— ============================================================
— sample code on using the SQL Server xml method exist()
— several samples shown
— Donabel Santos
— ============================================================

DECLARE @xmlSnippet XML
DECLARE @id SMALLINT
DECLARE @value VARCHAR(20)

SET @xmlSnippet =

<ninjaElement id="1">SQL Server Ninja</ninjaElement>
<ninjaElement id="2">SharePoint Ninja</ninjaElement>
<ninjaElement id="3">ASP.NET Ninja</ninjaElement>

— this is what we will look for
SET @id = 2
SET @value =’SQL Server Ninja’

— note exist() will return only either :
— 1 (true) or 0 (false)

— check if a node called ninjaElement exists
— at any level in the XML snippet
SELECT @xml.exist(‘//ninjaElement’)

— check if a node called bar exists
SELECT @xml.exist(‘//bar’)

— check if attribute id exists anywhere
SELECT @xml.exist(‘//@id’)

— check if attribute id exists within a ninjaElement tag
SELECT @xml.exist(‘//ninjaElement[@id]’)

— check if the id attribute equals to what we saved
— in the @id variable
SELECT @xml.exist(‘/ninjaElement[@id=sql:variable("@id")]’)

— check if the node text equals to what
— we saved in the @value variable
SELECT @xml.exist(‘/ninjaElement[text()=sql:variable("@value")]’)

[/sql]

SQLXML : How to Join Multiple XML Snippets (using query() and UNION ALL)

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

— ============================================================
— sample code on joining multiple XML snippets
— using query() and UNION ALL
— Donabel Santos
— ============================================================

— declare xml variables
DECLARE @firstXMLSnippet XML
DECLARE @secondXMLSnippet XML

— first XML snippet
SET @firstXMLSnippet =

<attributes>
<attribute id="13" name="EmpNo">
<item value="10" />
</attribute>
<attribute id="44" name="Position">
<item value="Manager" />
</attribute>
<attribute id="32" name="Address">
<item value="123 XYZ St." />
</attribute>
<attribute id="33" name="City">
<item value="Vancouver" />
</attribute>
</attributes>

— second XML snippet
SET @secondXMLSnippet =

<attribute id="37" name="Province">
<item value="BC" />
</attribute>
<attribute id="52" name="Comment">
<item value="SQL Server XML How To" />
</attribute>

— use query() to extract just the <attribute> elements
— join using UNION ALL
— add the root element back by using ROOT() option
SELECT @firstXMLSnippet.query(‘//attribute’)
UNION ALL
SELECT @secondXMLSnippet
FOR XML PATH (”), ROOT(‘attributes’), TYPE

–result
/*
<attributes>
<attribute id="13" name="EmpNo">
<item value="10" />
</attribute>
<attribute id="44" name="Position">
<item value="Manager" />
</attribute>
<attribute id="32" name="Address">
<item value="123 XYZ St." />
</attribute>
<attribute id="33" name="City">
<item value="Vancouver" />
</attribute>
<attribute id="37" name="Province">
<item value="BC" />
</attribute>
<attribute id="52" name="Comment">
<item value="SQL Server XML How To" />
</attribute>
</attributes>
*/

[/sql]

SQLXML : How To Save XML Query Results to a File Using BCP

One way to save your XML query results to the file system is by using bcp (bulk copy program).

Be aware of the following before deciding to use bcp for your regular export requirements:

  • bcp is a program external to SSMS. If you need to use this from within your scripts, you will need to enable xp_cmdshell. xp_cmdshell is an extended stored procedure that allows external command line processes to be executed from within SQL Server. Enabling xp_cmdshell is considered to be a big no no in terms of security because this opens up avenues for malicious attacks through SQL Server.
  • Depending on how much data you need to export, you may need to batch your export to overcome rowsize limitations of bcp.

If you intend to use bcp from within SSMS, you will need to enable xp_cmdshell first, otherwise you will get the following error:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

The following example walks you through enabling xp_cmdshell, and using bcp from within SSMS to save your XML query to an external file.

Read more

SQLXML : How to Bulk Load XML From a File Using OPENXML

Using OPENXML you can import XML documents into an XML variable, or into a table that has XML columns. The following is an example:

   1: -- ==========================================================================
   2: -- Object       : OPENXML1.sql
   3: -- Object Type  : Script
   4: -- Description  : Various examples
   5: -- Developer    : Donabel Santos
   6: -- Origin       : 2008/08/17
   7: -- Last Modified: 2008/10/04
   8: -- Notes        : 
   9: -- ==========================================================================
  10:  
  11: -- bulk load
  12: -- For this example, XML file must be saved in C:
  13: -- XML file also should specify UTF-8 encoding, ie:
  14: -- <?xml version="1.0" encoding="UTF-8"?>
  15:  
  16: DECLARE @XMLTable TABLE
  17: (
  18:    xmlcol XML
  19: )
  20:  
  21: INSERT INTO @XMLTable(xmlcol)
  22: SELECT
  23:     InvoicesXML
  24: FROM
  25: (
  26:     SELECT * 
  27:     FROM OPENROWSET(BULK 'c:invoice.xml',SINGLE_BLOB) AS Invoices
  28: ) AS Invoices(InvoicesXML)
  29:  
  30: SELECT *
  31: FROM @XMLTable
  32:  
  33:  

SQLXML : How to Alter Existing Column Schema (XSD)

Sometimes you may need to change schema definitions in existing columns.

The following shows an example of how you can do this:

   1: -- drop the XML schema from existing column definitions
   2: ALTER TABLE AD
   3:    ALTER COLUMN Title xml
   4:  
   5: -- DROP IF EXISTS
   6: IF    EXISTS (SELECT name 
   7:               FROM  sys.xml_schema_collections 
   8:               WHERE name='TitleSchema')
   9:    DROP  XML SCHEMA COLLECTION TitleSchema
  10: GO
  11:  
  12: -- new definition
  13: -- there can only be one Title element
  14: CREATE XML SCHEMA COLLECTION TitleSchema AS 
  15: '<?xml version="1.0" encoding="utf-8"?>
  16: <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  17:   <xsd:element name="Title">
  18:     <xsd:complexType>
  19:          <xsd:attribute name="Type" type="xsd:string" />
  20:          <xsd:attribute name="Value" type="xsd:string" use="optional" />
  21:     </xsd:complexType>
  22:   </xsd:element>
  23: </xsd:schema>'
  24: GO
  25:  
  26: -- IMPORTANT: before you add back the schema to the column 
  27: -- definition, make sure all values in your existing column 
  28: -- comply with the new schema definition
  29: ALTER TABLE AD
  30:    ALTER COLUMN Title xml(TitleSchema)

SQLXML : How to List Columns That Have the XML Data Type

   1: -- this lists the table catalog, table name, 
   2: -- column name, and data type
   3: SELECT 
   4:     TABLE_CATALOG,
   5:     TABLE_NAME,
   6:     COLUMN_NAME, 
   7:     DATA_TYPE 
   8: FROM 
   9:     INFORMATION_SCHEMA.COLUMNS
  10: WHERE 
  11:     DATA_TYPE = 'xml'
  12:  

 

   1: -- this lists the corresponding schemas
   2: SELECT 
   3:     DISTINCT
   4:     OBJECT_NAME(sys.columns.object_id)        AS 'TableName',
   5:     sys.columns.name                    AS 'ColName',
   6:     sys.xml_schema_collections.name            AS 'Schema' 
   7: FROM 
   8:     sys.columns
   9:     LEFT JOIN     sys.xml_schema_collections 
  10:     ON sys.columns.xml_collection_id = sys.xml_schema_collections.xml_collection_id
  11: ORDER BY 
  12:     OBJECT_NAME(sys.columns.object_id), sys.columns.name    
  13:  

SQLXML : How to List Schema Elements and Attributes

   1: SELECT
   2:     sys.xml_schema_collections.xml_collection_id    AS CollectionID,
   3:     sys.xml_schema_collections.name                 AS SchemaName,
   4:     sys.xml_schema_elements.name                    AS ElementName,
   5:     sys.xml_schema_attributes.name                  AS AttributeName
   6: FROM
   7:     sys.xml_schema_collections
   8:     INNER JOIN sys.xml_schema_attributes
   9:     ON sys.xml_schema_collections.xml_collection_id =  sys.xml_schema_attributes.xml_collection_id
  10:     INNER JOIN sys.xml_schema_elements
  11:     ON sys.xml_schema_collections.xml_collection_id = sys.xml_schema_elements.xml_collection_id
  12: WHERE
  13:     sys.xml_schema_collections.name NOT LIKE 'sys'

Sample Result:

SQLXML Sample Result - Elements and Attribute Names

« Older Entries Recent Entries »