SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sales Order Workshop Part IV


Sales Order Workshop Part IV

Author
Message
jacob sebastian
jacob sebastian
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4200 Visits: 2523
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/2912.asp

.
jkli
jkli
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 12

Where is 'orderHeader' and 'x' set?





panesofglass
panesofglass
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 104
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
stevemc
Old Hand
Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)

Group: General Forum Members
Points: 310 Visits: 696

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
tymberwyld
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2532 Visits: 275
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
stevemc
Old Hand
Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)

Group: General Forum Members
Points: 310 Visits: 696

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
tymberwyld
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2532 Visits: 275
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
jacob sebastian
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4200 Visits: 2523

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
jacob sebastian
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4200 Visits: 2523

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
jacob sebastian
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4200 Visits: 2523
You are right. I will correct this.

.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search