Zen of SQL

Hi I have two questions: 1. How do you change your...

2012-04-20T18:39:43.100-04:00

Hi I have two questions:
1. How do you change your below example into creating the table as well. I saw a post above stating select *... into, but I cant get it to work

INSERT INTO
Products
(sku, product_desc)
SELECT X.product.query('SKU').value('.', 'INT'),
X.product.query('Desc').value('.', 'VARCHAR(30)')
FROM (
SELECT CAST(x AS XML)
FROM
OPENROWSET
(
BULK 'C:\Products.xml', SINGLE_BLOB
) AS T(x)
) AS T(x)CROSS APPLY x.nodes('Products/Product') AS X(product);

2. if I have an xml file like below:

car

1
01/02/12
2
05/04/12

which as you can see has different hierarchy of data, how can the price history information be stored in another table called price_history which links to the product

Please find below the complete XML format I was re...

2012-04-04T13:44:52.391-04:00

Please find below the complete XML format I was referencing in last comment:

<*Products*>
<*Product*>
<*entry colname="SKU"*>1<*/entry*>
<*entry colname="Desc"*>Book<*/entry*>
<*/Product*>
<*Product*>
<*entry colname="SKU"*>2<*/entry*>
<*entry colname="Desc"*>DVD<*/entry*>
<*/Product*>
<*Product*>
<*entry colname="SKU"*>3<*/entry*>
<*entry colname="Desc"*>Video<*/entry*>
<*/Product*>
<*/Products*>

Thanks again!
Khaled

Thanks Plamen for your great article! I appreciat...

2012-04-04T13:34:42.491-04:00

Thanks Plamen for your great article!

I appreciate your feedback on what to modify in the query to parse an XML in the following format, where node names repeat, and are distinguished by their attribute values, yet also include the inline values I want to pull:

1
Book

2
DVD

3
Video

I realize that you have addressed the extraction of attribute values in the Shredding XML article, but I couldn't locate details on how to handle my particular case.

Thanks again for all your help!
Khaled

Reza, The best is to create a calendar table. See ...

2012-04-02T21:55:32.164-04:00

Reza,
The best is to create a calendar table. See the following link for more details: http://pratchev.blogspot.com/2007/12/auxiliary-tables.html. You can add the civic holidays to the calendar table and then calculation is a trivial query to sum the work hours between two dates.

Do you have any thought about how to include civic...

2012-04-02T19:07:40.500-04:00

Do you have any thought about how to include civic holidays to this logic as well? thx!

Hi, Ignore previous post. Here we can see data hi...

2012-03-29T06:47:44.192-04:00

Hi,

Ignore previous post. Here we can see data hierarchy properly.

We have xml in which we do have hierarchy like: -

topics
--topic
----term
----subtopic
------term
----subtopic
------term
--topic
topics

What I need to do is extract data from term under topic and put it under one column and then extract data from all subtopic/term under one topic and append them (separated by |) and put them under another column.

Topic Subtopics
----- ----------

Can anyone please let us know how to achieve same with the help of Stored Procedure.
Thanks in advance.

Hi, We have xml in which we do have hierarchy lik...

2012-03-29T06:44:26.711-04:00

Hi,

We have xml in which we do have hierarchy like: -

topics
topic
term
subtopic
term
subtopic
term
topic
topics
What I need to do is extract data from term under topic and put it under one column and then extract data from all subtopic/term under one topic and append them (separated by |) and put them under another column.

Topic Subtopics
----- ----------

Can anyone please let us know how to achieve same with the help of Stored Procedure.
Thanks in advance.

2012-03-29T06:13:15.220-04:00

This comment has been removed by the author.

Yes, this is documented in MSDN and SQL Server BOL...

2012-03-11T22:03:49.127-04:00

Yes, this is documented in MSDN and SQL Server BOL:
http://msdn.microsoft.com/en-us/library/ms190312.aspx
http://msdn.microsoft.com/en-us/library/ms191184.aspx

It works, but I can not find any books that talk a...

2012-03-11T19:28:50.771-04:00

It works, but I can not find any books that talk about it in more detail. I guess its something you have learn yourself from the MSDN library?

Thank you for the post. Could you give an advice. ...

2012-03-05T16:39:11.356-05:00

Thank you for the post. Could you give an advice. If I have the same problem, but I have not 4 pairs as in the example, but 25 pairs to unpivot.
Moreover, I have to do group by first of the resulting column of the dynamic table and then order by sum(second resulting column)

Also initial table contains more then 1 000 000 of rows. Is it good scenario for this task ? SQL has to manage group by and order by on the huge dynamic table without indexes.

May be it is exist another approach to get same result ?

Very useful ! Thank you :)

2012-02-23T16:22:56.787-05:00

Very useful ! Thank you :)

Hi Plamen, I'd like to make a simple spatial b...

2012-02-17T00:48:31.424-05:00

Hi Plamen,
I'd like to make a simple spatial based application on a mobile frame platform. Right now the local database on the phone is MS SQL Server Compact Edition 3.5 and the external db is IBM DB2 9.7. I will have soon some table with polygons and offices with latitudes and longitudes. The goal is to find (and possibly to display) the routes between 2 points, that in addition should belong to one region of the state. I am beginner in this, my question is just where to start, what do I need, reference etc.
Thanks, Georgi

Is it possible for this to work pulling from an ht...

2012-02-16T15:39:57.810-05:00

Is it possible for this to work pulling from an http file? I need to automate this process and pull from an xml file on the web.

Hi, I am trying to load an XML File using XML Sour...

2012-02-11T05:40:54.439-05:00

Hi,
I am trying to load an XML File using XML Source task in SSIS.
But its throwing the following error:
[XML Source [1]] Error: The component "XML Source" (1) was unable to process the XML data. Derived types are not allowed to be declared in source Xml documents. See element "Risk" with the attribute "xsi:type=House".
I don't how to add this attribute to the XSD Schema.
Can you please help me resolve it?

Good article -- thanks. I work with EAV a lot in ...

2012-02-08T14:58:19.341-05:00

Good article -- thanks. I work with EAV a lot in healthcare, where it can be highly useful because of the "sparse matrix" nature of the data. Question for you -- do you know of an algorithm or technique for transforming an EAV table to a flat table "on the fly" when the number of attributes that must become columns is potentially large and/or little is known about them a priori?

Your method involves knowing all of the attributes in advance, hard-coding the new table that will represent them as columns, and then constructing the query that transforms. I would like to be able to write a query that takes any EAV table about which I have little prior knowledge and transforms and presents the data in a flattened format.

Thanks in advance for any advice!

PS -- if this cannot be accomplished in SQL, I would also be interested in a programmatic algorithm using arrays.

I'm trying to get nested attributes to work - ...

2012-02-08T11:42:38.648-05:00

I'm trying to get nested attributes to work - can anyone give me an idea?

This is the XML:
<*Document*>
<*Database name="cpData"*>
<*PortID value="4946"*>
<*ClientInformation*>
<*Resultset*>
<*Group name="GroupDataResultRoot"*>
<*Group name="GroupSameChildren"*>
<*Group name="GroupPortfolioIDIndividualGroupOnly"*>
<*Field name="Portfolio_PortfolioID">4946
<*Field name="Portfolio_PortType">Group
<*Field name="Portfolio_Objective">P56BEANFR3

This is my test query:
Use Test_KTC
Insert Into PCConfig (PortfolioID,PortType,Objective)
Select X.pcconfig.query('Portfolio_PortfolioID').value('.', 'int'),
X.pcconfig.query('Portfolio_PortType').value('.','nchar(10)'),
X.pcconfig.query('Portfolio_Objective').value('.','int')

From(
Select Cast(x AS XML)
From OPENROWSET(
Bulk 'F:\test.xml',
Single_Blob) AS T(x)
) as T(x)
CROSS APPLY x.nodes('Document/cpData/PortID/ClientInformation/Resultset/Group/Group/Group') AS X(pcconfig);

SELECT PortfolioID,PortType,Objective
From PCCONFIG

If I try to use the data(@name) as mentioned earlier (Select X.pcconfig.query('Portfolio_PortfolioID(@name)').value('.', 'int'),), and it gives me this error: XQuery [T.x.query()]: There is no function '{http://www.w3.org/2004/07/xpath-functions}:Portfolio_PortfolioID()'

The XML can't be changed and clearly I'm missing something...

Thanks!

I have a question about how to pull just a section...

2012-01-27T18:49:17.920-05:00

I have a question about how to pull just a section of the file for instance My files would be like this
<"Catelog">
<"List_Products_Types">
<"Product_types">
<"Product_type">Dolls<"/Product_type">
<"Products">
<"Product">
<"ProductID">1<"/ProductID">
<"ProductName">barbie<"/ProductName">
<"/Product">
<"Product">
<"ProductID">2<"/ProductID">
<"ProductName">ken<"/ProductName">
<"/Product">
<"/Products">
<"Product_type">Toys<"/Product_type">
<"Products")
<"Product">
<"ProductID">10<"/ProductID">
<"ProductName">wagon<"/ProductName">
<"/Product">
<"Product">
<"ProductID">20<"/ProductID">
<"ProductName">bike<"/ProductName">
<"/Product">
<"/Products">
<"/List_Products_Types">
<"/Catelog">

And I only what to pull a list of the dolls I have tried a where clause but I muse be missing somthing

2012-01-27T18:42:56.098-05:00

This comment has been removed by the author.

2012-01-27T18:36:13.262-05:00

This comment has been removed by the author.

many thanks, this is exactly what I was looking fo...

2012-01-18T03:35:09.870-05:00

many thanks, this is exactly what I was looking for

Thank you Plamen for this post. I have not used C...

2012-01-17T10:39:24.908-05:00

Thank you Plamen for this post. I have not used Cross Apply or FOR XML PATH before and your post was able to solve a business problem for me, thanks again

Doug

Cool - thanks - works a treat - once I had the adj...

2012-01-11T04:48:54.621-05:00

Cool - thanks - works a treat - once I had the adjacency list I pulled that out as a digraph (DOT format) - easy to feed into a tool such as Graphviz and bingo - your nested set is drawn as a diagram - good to check visually that your structure is held properly. Many thanks again!

Nice. I've been looking for an example like th...

2012-01-10T14:58:56.999-05:00

Nice. I've been looking for an example like this. Thanks :)

Thanks for the Post!!

2012-01-05T14:36:16.520-05:00

Thanks for the Post!!