Sales Order Workshop Part IV
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/2912.asp
.
-
Where is 'orderHeader' and 'x' set?
-
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?
-
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)
-
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?
-
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)
-
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
-
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.
.
-
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.
.
-
You are right. I will correct this.
.
-
I just posted a reply above, where i presented an example which extracts values from attributes and XML nodes.
.
-
I just posted a reply above, which has an example of extracting values from xml attributes and nodes, using "value" method.
.
-
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
-
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)
.
-
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 26 total)
You must be logged in to reply to this topic. Login to reply