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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

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

.
jkli
jkli
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 12

Where is 'orderHeader' and 'x' set?





panesofglass
panesofglass
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 679

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
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 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
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 679

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
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1012 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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1012 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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1012 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