SQL Server Performance

XML duplicate tags issue

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by stefanoale, Jun 1, 2006.

  1. stefanoale New Member

    I'm having an issue pulling values from tags that have the same name as per below...

    This returns 2 values for customerID (GOOD!) Notice that it is attribute based!

    DECLARE @idoc int
    DECLARE @doc varchar(8000)
    Set @doc = '
    <ROOT>
    <Customer CustomerID="VINET" ></Customer>
    <Customer CustomerID="LILAS" ></Customer>
    </ROOT>
    '
    --Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
    -- Execute a SELECT statement that uses the OPENXML rowset provider.
    SELECT *
    FROM OPENXML (@idoc, '/ROOT/Customer',1)
    WITH (CustomerID varchar(10))

    -------------------------------------------------------

    BUT THIS RETURNS ONLY 1 VALUE (VERY BAD - ELEMENT BASED!)

    DECLARE @idoc int
    DECLARE @doc varchar(8000)
    Set @doc = '
    <ROOT>
    <Customer>
    <CustomerID>VINET</CustomerID>
    <CustomerID>VIghds</CustomerID>
    </Customer>
    </ROOT>
    '

    --Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
    -- Execute a SELECT statement that uses the OPENXML rowset provider.
    SELECT *
    FROM OPENXML (@idoc, '/ROOT/Customer',2)
    WITH (CustomerID varchar(10))


    WHY!?!? AND HOW CAN I GET 2 VALUES BACK ELEMENT BASED?!?

    Thanks a million.
  2. spacemonkey New Member

    Keep in mind that using element based setups result in different hierarchical structures. Using dot notation, the difference might be something like this from what you have above.

    root.Customer.attributes["CustomerID"]
    vs
    root.Customer.CustomerID.childNodes[0].value

    Give the following a shot.

    DECLARE @idoc int
    DECLARE @doc varchar(8000)
    Set @doc = '
    <ROOT>
    <Customer>
    <CustomerID CID="VINET" />
    <CustomerID CID="LILAS" />
    </Customer>
    </ROOT>
    '

    --Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
    -- Execute a SELECT statement that uses the OPENXML rowset provider.
    SELECT *
    FROM OPENXML (@idoc, '/ROOT/Customer/CustomerID',2)
    WITH (CID varchar(10) '@CID')

    John
  3. stefanoale New Member

    In case someone else needs this, here is the solution:

    DECLARE @idoc int
    DECLARE @doc varchar(8000)
    Set @doc = '<root><CustomerID>VINET</CustomerID><CustomerID>VIghds</CustomerID><CustomerID>VIghdfdss</CustomerID></root>'

    --Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
    -- Execute a SELECT statement that uses the OPENXML rowset provider.
    SELECT *
    FROM OPENXML (@idoc, '',2)

    This will return a nice able structure will all node information:
    0NULL1rootNULLNULLNULLNULLNULL
    201CustomerIDNULLNULLNULLNULLNULL
    523#textNULLNULLNULLNULLVINET
    301CustomerIDNULLNULLNULL2NULL
    633#textNULLNULLNULLNULLVIghds
    401CustomerIDNULLNULLNULL3NULL
    743#textNULLNULLNULLNULLVIghdfdss


    SWEET!

Share This Page