none
Retrieving OpenXml data without 'with' clause

    Question

  • I have a stored procedure which takes input as XML parameter which converts that into a table.My problem is that I don't to use 'with' clause in select statement when i retrieve data from openXml.

    Here My code.

    DECLARE @DocHandle int
    DECLARE @XmlDocument nvarchar(1000)
    SET @XmlDocument = N'<ROOT>
    <Customer CustomerID="VINET" ContactName="Paul Henriot">
       <Order OrderID="10248" CustomerID="VINET" EmployeeID="5" 
              OrderDate="1996-07-04T00:00:00">
          <OrderDetail ProductID="11" Quantity="12"/>
          <OrderDetail ProductID="42" Quantity="10"/>
       </Order>
    </Customer>
    <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
       <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" 
              OrderDate="1996-08-16T00:00:00">
          <OrderDetail ProductID="72" Quantity="3"/>
       </Order>
    </Customer>
    </ROOT>'
    -- Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
    -- Execute a SELECT statement using OPENXML rowset provider.
    SELECT *
    FROM OPENXML (@DocHandle,'/ROOT/Customer',1) WITH (CustomerID  varchar(10),---I dont want to use this
                
    EXEC sp_xml_removedocument @DocHandle

     
    Friday, May 16, 2014 7:00 AM

Answers

  • Well, the WITH clause is optional. :-) If you remove it, you get a so-called
    edge table. Exactly what one would use it for, I have not been able to
    figure out.

    But you don't need to use OPENXML at all. Here's an example using XQuery
    instead:

    DECLARE @x xml
    SELECT @x =
      N'<Orders>
          <Order OrderID="13000" CustomerID="ALFKI"
                 OrderDate="2006-09-20Z" EmployeeID="2">
             <OrderDetails ProductID="76" Price="123" Qty = "10"/>
             <OrderDetails ProductID="16" Price="3.23" Qty = "20"/>
          </Order>
          <Order OrderID="13001" CustomerID="VINET"
                 OrderDate="2006-09-20Z" EmployeeID="1">
             <OrderDetails ProductID="12" Price="12.23" Qty = "1"/>
          </Order>
        </Orders>'
    SELECT OrderID    = T.Item.value('@OrderID[1]',    'int'),
           CustomerID = T.Item.value('@CustomerID[1]', 'nchar(5)'),
           OrderDate  = T.Item.value('@OrderDate[1]',  'datetime'),
           EmployeeId = T.Item.value('@EmployeeID[1]', 'smallint')
    FROM   @x.nodes('/Orders/Order') AS T(Item)

    SELECT OrderID    = A.Item.value('@OrderID[1]',    'int'),
           ProductID  = B.Item.value('@ProductID[1]',  'smallint'),
           Price      = B.Item.value('@Price[1]',      'decimal(10,2)'),
           Qty        = B.Item.value('@Qty[1]',        'int')
    FROM   @x.nodes('/Orders/Order') AS A(Item)
    CROSS  APPLY A.Item.nodes('OrderDetails') AS B (Item)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 16, 2014 7:26 AM

All replies

  • Well, the WITH clause is optional. :-) If you remove it, you get a so-called
    edge table. Exactly what one would use it for, I have not been able to
    figure out.

    But you don't need to use OPENXML at all. Here's an example using XQuery
    instead:

    DECLARE @x xml
    SELECT @x =
      N'<Orders>
          <Order OrderID="13000" CustomerID="ALFKI"
                 OrderDate="2006-09-20Z" EmployeeID="2">
             <OrderDetails ProductID="76" Price="123" Qty = "10"/>
             <OrderDetails ProductID="16" Price="3.23" Qty = "20"/>
          </Order>
          <Order OrderID="13001" CustomerID="VINET"
                 OrderDate="2006-09-20Z" EmployeeID="1">
             <OrderDetails ProductID="12" Price="12.23" Qty = "1"/>
          </Order>
        </Orders>'
    SELECT OrderID    = T.Item.value('@OrderID[1]',    'int'),
           CustomerID = T.Item.value('@CustomerID[1]', 'nchar(5)'),
           OrderDate  = T.Item.value('@OrderDate[1]',  'datetime'),
           EmployeeId = T.Item.value('@EmployeeID[1]', 'smallint')
    FROM   @x.nodes('/Orders/Order') AS T(Item)

    SELECT OrderID    = A.Item.value('@OrderID[1]',    'int'),
           ProductID  = B.Item.value('@ProductID[1]',  'smallint'),
           Price      = B.Item.value('@Price[1]',      'decimal(10,2)'),
           Qty        = B.Item.value('@Qty[1]',        'int')
    FROM   @x.nodes('/Orders/Order') AS A(Item)
    CROSS  APPLY A.Item.nodes('OrderDetails') AS B (Item)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 16, 2014 7:26 AM
  • can I use * from @x.nodes,intend of specifying the columns.i.e

    SELECT *
    FROM   @x.nodes('/Orders/Order') 

    because i don't know what the columns will be.
    Friday, May 16, 2014 7:30 AM
  • you can

    in fact you can also retrieve column names along with the result from the xml

    see an example here

    http://visakhm.blogspot.in/2012/10/shred-data-as-well-as-metadata-from-xml.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, May 16, 2014 7:56 AM