SQL Server Performance

Trying to Select Data from XML Document and Insert It Into a Table, but Having Trouble with XML Namespace

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by lcerni, Aug 5, 2008.

  1. lcerni New Member

    My manager has asked me to look into the following question for him. Does anyone have any solutions?
    ===================================================
    In this example, I created a simple XML Document, with a namespace specified. I then utilize the ;WITH XMLNAMESPACES statement prior to the select clause to select the data. This works. If I leave the ;WITH XMLNAMESPACES clause out, then it doesn't work. This is as expected.
    --WORKS
    DECLARE @xmlDoc xml
    SET @xmlDoc ='
    <myDoc xmlns="http://mysample.com/Schema/myDoc.xsd">
    <Customer>
    <cust_id>5508</cust_id>
    <cust_name>ABC Corp</cust_name>
    </Customer>
    </myDoc>'
    ;WITH XMLNAMESPACES
    (
    DEFAULT 'http://mysample.com/Schema/myDoc.xsd'
    )
    SELECT
    col.value('cust_id[1]','int') AS cust_id,
    col.value('cust_name[1]','varchar(32)') AS cust_name
    FROM @xmlDoc.nodes('myDoc/Customer') AS Customer(col)
    ===================================================
    This example is similar, but it also inserts the selected data into a temporary table object, however the namespace is not included in this XML document, therefore I did not have to use the ;WITH XMLNAMESPACES clause. This also works fine.
    --WORKS
    DECLARE @xmlDoc xml
    SET @xmlDoc ='
    <myDoc>
    <Customer>
    <cust_id>5508</cust_id>
    <cust_name>ABC Corp</cust_name>
    </Customer>
    </myDoc>'
    Declare @tmp Table(cust_id int, cust_name varchar(32))
    INSERT INTO @tmp(cust_id, cust_name)
    SELECT
    col.value('cust_id[1]','int') AS cust_id,
    col.value('cust_name[1]','varchar(32)') AS cust_name
    FROM @xmlDoc.nodes('myDoc/Customer') AS Customer(col)
    SELECT cust_id, cust_name FROM @tmp
    ===================================================
    Now if I try to insert into the temporary table object, but now include the namespace and the ;WITH XMLNAMESPACES clause, i get an error: "Incorrect syntax near ';'."
    It seems that the Insert statement isn't accepting the ;WITH XMLNAMESPACES section, as it is expecting a SELECT or VALUES section instead.
    --DOESN'T WORK
    DECLARE @xmlDoc xml
    SET @xmlDoc ='
    <myDoc xmlns="http://mysample.com/Schema/myDoc.xsd">
    <Customer>
    <cust_id>5508</cust_id>
    <cust_name>ABC Corp</cust_name>
    </Customer>
    </myDoc>'
    Declare @tmp Table(cust_id int, cust_name varchar(32))
    INSERT INTO @tmp(cust_id, cust_name)
    ;WITH XMLNAMESPACES (DEFAULT 'http://mysample.com/Schema/myDoc.xsd')
    SELECT
    col.value('cust_id[1]','int') AS cust_id,
    col.value('cust_name[1]','varchar(32)') AS cust_name
    FROM @xmlDoc.nodes('myDoc/Customer') AS Customer(col)
    SELECT cust_id, cust_name FROM @tmp
    =====================================================
    Any ideas on how to do this? I realize I can strip the namespace from the document, but this really isn't the solution I'm looking for.
    Thanks in advance for any suggestions you may have
  2. lcerni New Member

    Never mind. I figured it out. The WITH statement should have came before the INSERT statement.DECLARE @xmlDoc xml
    SET @xmlDoc ='
    <myDoc xmlns="http://mysample.com/Schema/myDoc.xsd">
    <Customer>
    <cust_id>5508</cust_id>
    <cust_name>ABC Corp</cust_name>
    </Customer>
    </myDoc>'
    Declare @tmp Table(cust_id int, cust_name varchar(32))
    ;WITH XMLNAMESPACES (DEFAULT 'http://mysample.com/Schema/myDoc.xsd')
    INSERT INTO @tmp(cust_id, cust_name)
    SELECT
    col.value('cust_id[1]','int') AS cust_id,
    col.value('cust_name[1]','varchar(32)') AS cust_name
    FROM @xmlDoc.nodes('myDoc/Customer') AS Customer(col)
    SELECT cust_id, cust_name FROM @tmp

Share This Page