XML Workshop 26 – SELECT * FROM XML

  • Comments posted to this topic are about the item XML Workshop 26 – SELECT * FROM XML

    .

  • Great Article. I really enjoy reading this.

    Many thanks for thinking 'SELECT *'. I agree that Select * now means bring me everything.

    Kind Regards,


    Kindest Regards,

    Pinal Dave
    sqlauthority.com

  • what about querying attributes?

  • Yes, you can also query attributes.

    .

  • can you show an example of querying attributes please?

    thanks in advance

  • Here is a simple example:

    DECLARE @xml XML

    SELECT @xml = '<employee name="jacob"/>'

    SELECT * FROM XMLTable(@xml)

    WHERE NodeType = 'attribute'

    .

  • Thank you! very useful!

  • Excellent - I can see this being very useful.

    There's a couple of typos in the function definition if your server happens to be case sensitive.

    Line 087 p.position should be p.Position

    Line 166 XmlData should be XMLData

  • Thanks, Fixed both places.

    .

  • Fast work - thanks!

  • This is awesome work! We have a production system using a custom form XML structure and this will be tremendously helpful. Thanks!

  • Very nice! I tucked this one away for later, Thank You.

  • Sebastian,

    This is very powerful stuff!

    Very nice to be able to automatically produce XQuery paths statements.

    Thanks,

  • Excellent!

  • One potential problem and improvement that could be made to the function.

    Support for namespaces?

    For example:

    DECLARE @x XML

    SELECT @x = '

    <BulkDealComponentInsert xmlns="http://www.mycomp.com/lbg.paris.load.bulkdealcomponentinsert.xsd" RequestId="150134" BatchNum="25">

    <Deal DealCode="2862868LN|SWAPTION" DealVersion="10">

    <OrgCode>TPGR|SWTN|BERMB2B</OrgCode>

    <ProductCode>SWAPTION|BERMUDAN|NOT_KNOCKED</ProductCode>

    <DealTypeCode>SWAPTION</DealTypeCode>

    <DealStatusCode>VER</DealStatusCode>

    <PartyCode>Party|BIPS</PartyCode>

    <VersionDateTime>2009-11-24T14:50:47Z</VersionDateTime>

    <MaturityDate>2012-10-23Z</MaturityDate>

    <CompanyCode>LTSB</CompanyCode>

    <DealerCode>DJ</DealerCode>

    <ProductGroupCode>SWAPTION</ProductGroupCode>

    <ProductTypeCode>SWAPTION</ProductTypeCode>

    <IsPayer>false</IsPayer>

    <DealDate>2007-10-19T00:00:00Z</DealDate>

    <Component ComponentCode="Deal.Level">

    <ComponentTypeCode>Deal.Type</ComponentTypeCode>

    <BookCode>SWTN</BookCode>

    <BuySell>S</BuySell>

    </Component>

    </Deal>

    </BulkDealComponentInsert>'

    SELECT @x.value('BulkDealComponentInsert[1]/Deal[1]/OrgCode[1]', 'varchar(100)') AS OrgCode

    Returns NULL.

    Thanks,

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply