Post reply

XML Workshop 26 – SELECT * FROM XML

  • June 29, 2010 at 9:51 pm

    #139608

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

    .

  • June 30, 2010 at 12:51 am

    #1186871

    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

  • June 30, 2010 at 1:23 am

    #1186881

    what about querying attributes?

  • June 30, 2010 at 1:55 am

    #1186899

    Yes, you can also query attributes.

    .

  • June 30, 2010 at 2:14 am

    #1186910

    can you show an example of querying attributes please?

    thanks in advance

  • June 30, 2010 at 3:01 am

    #1186932

    Here is a simple example:

    DECLARE @xml XML

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

    SELECT * FROM XMLTable(@xml)

    WHERE NodeType = 'attribute'

    .

  • June 30, 2010 at 3:18 am

    #1186936

    Thank you! very useful!

  • June 30, 2010 at 3:25 am

    #1186938

    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

  • June 30, 2010 at 3:35 am

    #1186941

    Thanks, Fixed both places.

    .

  • June 30, 2010 at 4:06 am

    #1186951

    Fast work - thanks!

  • June 30, 2010 at 6:43 am

    #1187020

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

  • June 30, 2010 at 7:00 am

    #1187030

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

  • June 30, 2010 at 8:30 am

    #1187113

    Sebastian,

    This is very powerful stuff!

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

    Thanks,

  • June 30, 2010 at 8:31 am

    #1187115

    Excellent!

  • June 30, 2010 at 8:36 am

    #1187120

    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