Category: xml

Oct 30 2012

How to use Notepad++ to fix invalid date formats.

I was just sent some xml files that had dates in this format…

31/09/2012 (dd/mm/yyyy)

This date format won’t work in some of our systems so I needed to change some of the dates to the format 09/31/2012 (mm/dd/yyyy).

Notepad++ to the rescue.

I opened up the Find/Replace dialog (CTRL-H) in n++ and set the Search Mode to Regular expression.

In the “Find what:” box I put this…
(\d{2})/(\d{2})/(\d{4})

In the “Replace with:” box I put this…
$2/$1/$3

It worked perfectly!!

Seth

Aug 16 2012

How do you select from a xml column in sql server if the column is not a singleton

I am trying to query an xml column among other...the following query is working fine...
SELECT
            OrderID,
            AccountNumber, 
            ItemID,
            substring(replace(lower(s.Street),' ',''),1,8) 
                + substring(replace(lower(s.City),' ',''),1,8) 
                + substring(replace(lower(s.State),' ',''),1,8) 
                + substring(replace(s.ZipCode,' ',''),1,5)AddressHash,
            ShipName,
            Street,
            Street2
            City,
            State,
            ZipCode,
            OrderDate
    FROM   (SELECT UpwardOrderID, AccountNumber, UpwardLeagueID,
                    /* NOTE THAT THIS SYNTAX WORKS ONLY WORKS BECAUSE THE NODES ARE SINGLETONS. */
                   x.value('(./ShipTo/Name)[1]', 'VARCHAR(255)')                                                AS ShipName,
                   x.value('(./ShipTo/Street1)[1]', 'VARCHAR(255)')                                             AS Street,
                   x.value('(./ShipTo/Street2)[1]', 'VARCHAR(255)')                                             AS Street2,
                   x.value('(./ShipTo/Subdivision1)[1]', 'VARCHAR(255)')                                        AS City,
                   x.value('(./ShipTo/Subdivision2)[1]', 'VARCHAR(255)')                                        AS State,
                   x.value('(./ShipTo/PostalCode)[1]', 'VARCHAR(255)')                                          AS ZipCode,
                   x.value('(./Order/ClientOrderDate)[1]', 'DATETIME')                                          AS OrderDate
                   --x.value('(./ShippingMethods/ShippingMethod/ID)[../Selected/text()=1]','VARCHAR(255)')
            FROM   av_order CROSS APPLY orderXML.nodes('/Order/ShippingInformation') t(x)
            WHERE orderXML Is Not Null) s
This query is working fine except for the last column I am trying to select in the FROM subquery. The difference is that column (ShippingMethod) is NOT a singleton. The XML contains all of the shipping methods and I want to select the ID of the SELECTED shipping method. Here is what that part of the XML looks like...
<Order>...
    <ShippingInformation>
        <ShipTo>
            <Name>DONT SHOW</Name>
            <Attention>DONT SHOW</Attention>
            <Street1>DONT SHOW</Street1>
            <Street2 />
            <Subdivision1>DONT SHOW</Subdivision1>
            <Subdivision2>IL</Subdivision2>
            <PostalCode>62092</PostalCode>
            <CountryCode>US</CountryCode>
            <AllowEmptyShipTo>0</AllowEmptyShipTo>
            <ContactInfo>DONT SHOW</ContactInfo>
        </ShipTo>
        <ShippingMethods>
            <ShippingMethod>
                <ID>UPSGROUND</ID>
                <Selected>1</Selected>
                <Cost>134.08</Cost>
            </ShippingMethod>
            <ShippingMethod>
                <ID>PICKUP</ID>
                <Selected>0</Selected>
                <Cost>0</Cost>
            </ShippingMethod>
            <ShippingMethod>
                <ID>UPS3DAY</ID>
                <Selected>0</Selected>
                <Cost>288.46</Cost>
            </ShippingMethod>
            <ShippingMethod>
                <ID>UPS2DAY</ID>
                <Selected>0</Selected>
                <Cost>347.91</Cost>
            </ShippingMethod>
            <ShippingMethod>
                <ID>UPSNEXTBUSDAY</ID>
                <Selected>0</Selected>
                <Cost>956.73</Cost>
            </ShippingMethod>
        </ShippingMethods>
    </ShippingInformation>
...</Order>
What do I do to select the SELECTED shipping method ID? Seth