Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Sales Order Workshop Part IV Expand / Collapse
Author
Message
Posted Monday, March 12, 2007 10:04 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/2912.asp

.
Post #350782
Posted Thursday, April 5, 2007 9:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 2:56 PM
Points: 8, Visits: 12

Where is 'orderHeader' and 'x' set? 




Post #356300
Posted Thursday, April 5, 2007 1:24 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 17, 2014 8:51 AM
Points: 42, 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?
Post #356402
Posted Thursday, April 5, 2007 5:02 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 10:36 AM
Points: 95, Visits: 577

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)

 

Post #356451
Posted Wednesday, April 11, 2007 10:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 2, 2013 8:21 AM
Points: 283, Visits: 268
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?



Post #357575
Posted Wednesday, April 11, 2007 1:09 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 10:36 AM
Points: 95, Visits: 577

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)

Post #357632
Posted Wednesday, April 11, 2007 2:00 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 2, 2013 8:21 AM
Points: 283, Visits: 268
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



Post #357638
Posted Friday, April 13, 2007 3:53 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523

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.



.
Post #358130
Posted Friday, April 13, 2007 4:05 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523

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.



.
Post #358133
Posted Friday, April 13, 2007 4:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
You are right. I will correct this.

.
Post #358136
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse