Sales Order Workshop Part IV

  • jacob sebastian

    SSChampion

    Points: 11812

    Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/2912.asp

    .

  • jkli

    SSC Enthusiast

    Points: 132

    Where is 'orderHeader' and 'x' set? 

  • panesofglass

    SSC-Addicted

    Points: 440

    This is great stuff! However, I am working with xml files that contain info in the attributes and between the tags. How would I pull the data from between tags?

  • stevemc

    Say Hey Kid

    Points: 712

    Enjoyed the series. Found I had to make this change in the header insert.  Otherwise I get a datetime conversion error. 

     51         INSERT INTO OrderHeader (OrderNumber, OrderDate, CustomerNumber, OrderDate)   

       52         SELECT

       53             x.header.value('@orderNumber[1]', 'varchar(20)') as OrderNumber,

       54             x.header.value('@customerNumber[1]', 'varchar(20)') as customerNumber,

       55             x.header.value('@orderDate[1]', 'datetime') as OrderDate

       56             FROM @OrderInfo.nodes('//salesOrder') as x(header)

     

  • tymberwyld

    SSCertifiable

    Points: 7810

    This really doesn't explain anything about selecting values from Elements in the Xml. You should have explained both scenarios: 1.) having values as Attributes (which you did) and 2.) having values at the Element level.

    For example, I have the following Xml and want to retrieve it as a select statement. Everything is fine, except that all my columns are of DataType "XML"! How would I get these to be of a certain type (i.e. Int, VarChar, etc.)??

    Declare @FeatureData Xml

    Set @FeatureData = '

    <DocumentElement>

    <Features>

    <TableID>-1</TableID>

    <ID>-1</ID>

    <Name>Bering Sea</Name>

    <Prefix />

    <Suffix />

    <FeatureType />

    <Version>06.10</Version>

    <FileID>02013</FileID>

    <LineID>1</LineID>

    </Features>

    </DocumentElement>'

    Select

    doc.rows.query('ID/text()') As ID,

    doc.rows.query('Name/text()') As Name,

    doc.rows.query('Prefix/text()') As Prefix,

    doc.rows.query('Suffix/text()') As Suffix,

    doc.rows.query('FeatureType/text()') As FeatureType,

    doc.rows.query('Version/text()') As Version,

    doc.rows.query('FileID/text()') As FileID,

    doc.rows.query('LineID/text()') As LineID

    FROM @FeatureData.nodes('//Features') As doc(rows)

    Any ideas?

  • stevemc

    Say Hey Kid

    Points: 712

    Not sure WHY this works, but it works.  I changed the Path in the FROM clause to include the /ID node, and then use .value method to get the value -1 as an int.  Note the pathing in the other .query method has to change.  Not sure how to apply .value to those the get the values out.  Surely, this is not that complicated.

    Declare @FeatureData Xml

    Set @FeatureData = '

    <DocumentElement>

    <Features>

    <TableID>-1</TableID>

    <ID>-1</ID>

    <Name>Bering Sea</Name>

    <Prefix />

    <Suffix />

    <FeatureType />

    <Version>06.10</Version>

    <FileID>02013</FileID>

    <LineID>1</LineID>

    </Features>

    </DocumentElement>'

    Select

    doc.rows.query('../ID/text()') As ID,

    doc.rows.query('../Name/text()') As Name,

    doc.rows.query('../Prefix/text()') As Prefix,

    doc.rows.query('../FileID/text()') As FileID,

    doc.rows.query('../LineID/text()') As LineID,

    doc.rows.value('.','int')

    FROM @FeatureData.nodes('//Features/ID') As doc(rows)

  • tymberwyld

    SSCertifiable

    Points: 7810

    Never mind, I figured it out! This Xml crap is definitely a LOT harder to figure out than OPENXML. At least with OPENXML you could guess better what the parser was doing.

    Anyway, here's the article that lead me to the solution:

    http://msdn2.microsoft.com/en-us/library/ms345115.aspx

    Do a search for: "Example: use of value()"

    And here's the solution. Notice the parentesis around each element name and the position of it "[1]". Apparently the parser is too stupid to understand XPath and it doesn't know that there is 1 or many Elements called (for example) "ID". So, it just ASSUMES that there will be many of that same element and just throws an error! So, even when you just have 1 element within your root-level nodes(), you still need to specify that there is...only one element!

    Select

    doc.rows.value('(./ID)[1]', 'int') As ID,

    doc.rows.value('(./Name)[1]', 'varchar(100)') As Name,

    doc.rows.value('(./Prefix)[1]', 'varchar(5)') As Prefix,

    doc.rows.value('(./Suffix)[1]', 'varchar(5)') As Suffix,

    doc.rows.value('(./FeatureType)[1]', 'varchar(5)') As FeatureType,

    doc.rows.value('(./Version)[1]', 'varchar(10)') As Version,

    doc.rows.value('(./FileID)[1]', 'varchar(5)') As FileID,

    doc.rows.value('(./LineID)[1]', 'int') As LineID

    From @Data.nodes('//Features') As doc(rows)

    And since Xml is basically all Text, here's the solution to cleanup the Empty Strings into NULL values:

    Select

    ID,

    Name,

    Case When LTrim(RTrim(Prefix)) = '' Then Null Else LTrim(RTrim(Prefix)) End As Prefix,

    Case When LTrim(RTrim(Suffix)) = '' Then Null Else LTrim(RTrim(Suffix)) End As Suffix,

    Case When LTrim(RTrim(FeatureType)) = '' Then Null Else LTrim(RTrim(FeatureType)) End As FeatureType,

    Case When LTrim(RTrim(Version)) = '' Then Null Else LTrim(RTrim(Version)) End As Version,

    Case When LTrim(RTrim(FileID)) = '' Then Null Else LTrim(RTrim(FileID)) End As FileID,

    LineID

    From (

    Select

    doc.rows.value('(./ID)[1]', 'int') As ID,

    doc.rows.value('(./Name)[1]', 'varchar(100)') As Name,

    doc.rows.value('(./Prefix)[1]', 'varchar(5)') As Prefix,

    doc.rows.value('(./Suffix)[1]', 'varchar(5)') As Suffix,

    doc.rows.value('(./FeatureType)[1]', 'varchar(5)') As FeatureType,

    doc.rows.value('(./Version)[1]', 'varchar(10)') As Version,

    doc.rows.value('(./FileID)[1]', 'varchar(5)') As FileID,

    doc.rows.value('(./LineID)[1]', 'int') As LineID

    From @Data.nodes('//Features') As doc(rows)

    ) T1

  • jacob sebastian

    SSChampion

    Points: 11812

    It is set from the "FROM" clause. Look at the following:

    ........FROM @x.nodes('//salesOrder') AS x(header)

    Does this answer your question? It is a type of alias that we assign to the resultset.

    .

  • jacob sebastian

    SSChampion

    Points: 11812

    hi ryan,

    Let me present a quick example:

    <orderInfo>

    <item code="A001" category="FOOD" subcategory="Candies">

    <description>Nestle Munch</description>

    <qty>10</qty>

    <rate>11.25</rate>

    </item>

    <item code="A002" category="FOOD" subcategory="Biscuits">

    <description>Britania Good Day</description>

    <qty>15</qty>

    <rate>12.25</rate>

    </item>

    </orderInfo>

    The following example extracts the attribute values:

    SELECT

    x

    .item.value('@code[1]', 'VARCHAR(20)') AS ItemCode,

    x

    .item.value('@category[1]', 'VARCHAR(20)') AS category,

    x

    .item.value('@subcategory[1]', 'VARCHAR(20)') AS subcategory

    FROM

    @x.nodes('//orderInfo/item') AS x(item)

     

    The following example extracts the values from xml nodes.

    SELECT

    x

    .item.value('description[1]', 'VARCHAR(20)') AS description,

    x

    .item.value('qty[1]', 'INT') AS qty,

    x

    .item.value('rate[1]', 'FLOAT') AS rate

    FROM

    @x.nodes('//orderInfo/item') as x(item)

    I am coming up with a new article which shows some advanced XML processing. I will present a few more practical examples in it. It will be on in the next few weeks.

    .

  • jacob sebastian

    SSChampion

    Points: 11812

    You are right. I will correct this.

    .

  • jacob sebastian

    SSChampion

    Points: 11812

    I just posted a reply above, where i presented an example which extracts values from attributes and XML nodes.

    .

  • jacob sebastian

    SSChampion

    Points: 11812

    I just posted a reply above, which has an example of extracting values from xml attributes and nodes, using "value" method.

    .

  • panesofglass

    SSC-Addicted

    Points: 440

    Thanks for the additional info. That makes a lot of sense. One more question: how do you incorporate namespaces into the processing? Do you have to just pull the namespaces out, or can you include the, for example, 'my:element' in its entirety. Apologies if this has already been addressed. I didn't see it above.

    Thanks,

    Ryan

  • jacob sebastian

    SSChampion

    Points: 11812

    Hi Ryan,

    the following example shows how to take values from a namespace.

    declare

    @x xml

    set

    @x = '

    <ns0:root xmlns:ns0="http://schemas.microsoft.com/sqlserver/2004/

    07/adventure-works/ProductModelManuInstructionsModified">

    <ns0:Location LocationID="100" SetupHours="10.4"

    MachineHours="10.4" LaborHours="10.4" LotSize="10.4" />

    </ns0:root>'

    select

    x

    .ns.value('@LocationID[1]', 'varchar(10)') as LocationID

    from

    @x

    .nodes('declare namespace ns0="http://schemas.microsoft.com/sqlserver/2004/

    07/adventure-works/ProductModelManuInstructionsModified";/ns0:root/ns0:Location'

    ) as x(ns)

    .

  • Hans Lindgren

    SSChampion

    Points: 10494

    tymberwyld:

    Why not use NULLIF( LTrim(RTrim(Prefix)) , '' ) instead of Case When LTrim(RTrim(Prefix)) = '' Then Null Else LTrim(RTrim(Prefix)) End As Prefix?

    Regards,

    Hanslindgren

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

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