Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    22

    Question Unanswered: Help with ;WITH XMLNAMESPACES

    I'm trying to select data from an XML document and insert it into a table, but I'm having trouble with the XML Namespace. Below are examples with commentary to explain what I'm trying to do:

    ================================================== =

    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,
    Roby2222

  2. #2
    Join Date
    Dec 2003
    Posts
    22

    Talking Figured it out

    Ok, we just figured this out. Simply put the ;WITH XMLNAMESPACES before the Insert statement as such:


    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

    Thanks,
    Roby2222

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •